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';

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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)