Question 114937 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there a "local timezone" suffix?

Is there any convention to add in a local timezone (whatever the user has set it as) in a database?

For example, like I can do the following for UTC time:

  • 2014-01-01 01:02:03Z

Is there something like the following to mean local time?

  • 2014-01-01 01:02:03L

Or some other suffix where it can either pick up the user’s system time or take it from a variable that can be set? For example, something like (for Postgres):

ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';
Tags:

4

Answers


  1. 0

    Just treat the undecorated time as a timestamp with time zone:

    richard=> SET timezone='Europe/London';
SET
richard=> SELECT '2022-08-27 21:42:22.25891'::timestamptz;
         timestamptz          
------------------------------
 2022-08-27 21:42:22.25891+01
(1 row)

richard=> SET timezone='Europe/Paris';
SET
richard=> SELECT '2022-08-27 21:42:22.25891'::timestamptz;
         timestamptz          
------------------------------
 2022-08-27 21:42:22.25891+02
(1 row)
    Login or Signup to reply.
  2. 0

    postgres would use the system timezone, or what you called local, if no timezone is specified. see manual

    If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

    …

    Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time.

    Login or Signup to reply.
  3. 0

    Your concept is flawed for a couple of reasons:

    1. A user in Germany connects to a Web server in England that connects to a database server is America. What constitutes local?

    2. More to the point Postgres does not store the time zone in with time zone fields. So you will not recover the entered time zone on data retrieval.

    If you are dealing with multiple time zones then the field you need to use is timestamp with time zone. This will rotate entered timestamp values to UTC for storage. You now have a fixed point in time that you can rotate to whatever ‘local’ time you want on retrieval.

    Login or Signup to reply.
  4. 0

    This has nothing to do with Postgres.

    The format you’re asking about is ISO 8601. Specifically in that format, the absence of a Z or an offset such as -07:00 or +05:30 is defined as "local time".

    So what you are looking for is a string without an offset, such as 2014-01-01T01:02:03.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search