Specify the time zone to use as the reference time zone

Question

Based on the first two answers, the question was unclear how it was originally published, so I completely rewrite it:

The next question concerns only how and what data is stored, and does not have any form or form for transforming data after extraction. Therefore, converting in SELECT to the required time zone is not an appropriate answer.

When a value is inserted into a time stamp with a time zone field, it is retrieved (and therefore presumably stored) with a time stamp converted to the local time zone of the database at the time of insertion.

Thus, the timestamp inserted as 2012-01-01 00:00:00+00:00 is retrieved as 2011-12-31 19:00:00-05 , where the local time zone of the database during insertion was -05 . Timestamps that were inserted during daylight saving time when the database was at -04 are returned using the -04 time zone.

I want all timestamps to use an arbitrary time zone when saving (and, therefore, all of them were received without additional work with this time zone). That is, if the server revolves around the planet, all times will be at +00:00 (arbitrary time zone) instead of -12:00 to +12:00 .

Is it possible to insert a timestamp with a timezone column so that all timestamps are kept relative to an arbitrary timezone? If so, how?

---

Followed by.

When you insert a value in the timestamp with time zone field, it is converted to the current time zone of the server.

Example. If I insert a value specifying a time zone of -1 , receiving it returns the time at -5 (the time zone of the server at the time of its installation).

Is it possible to indicate that it should be stored in an arbitrary time zone?

Note. This question is not about how to convert the returned time to another time zone, it depends on how the time is stored.

Answer 1

You must keep the time zone offset in addition to the timestamp .

As @Milen already explained (and related to manual ): a timestamp only saves time (as an abstract value). The time zone modifier is not saved, it only serves to set the timestamp relative to UTC .

Consider the following demo:

 -- DROP TABLE tbl; CREATE TEMP TABLE tbl (id int, myts timestamptz, mytz interval); INSERT INTO tbl VALUES (1, now() , EXTRACT (timezone from now()) * interval '1s') ,(2, '2012-01-01 00:00-05', interval '-5h') ,(3, '2012-01-01 00:00+04', interval '4h') ,(4, '2012-11-11 20:30+03', interval '3h'); SELECT * ,(myts AT TIME ZONE mytz)::text || CASE WHEN mytz > '0:0' THEN '+' ELSE '' END || to_char(mytz, 'FMHH24:mi') AS timestamp_at_origin FROM tbl; 

Run it locally to see. Pay particular attention to the details of the AT TIME ZONE design and how I retrieve the time zone from (local!) timestamp with time zone .
now() for brevity returns timestamp with time zone or timestamptz .

 EXTRACT (timezone from now()) * interval '1s' 

timestamp_at_origin displays a timestamp with a time zone, as shown at the beginning. If I understand your question, then this is what you are looking for.
You can improve formatting.

You might be interested in this related question , which sheds some light on the ambiguities and pitfalls of time zones.

Answer 2

When you insert a value into the timestamp with time zone field, what is actually happening is the timestamp converted to UTC. Another thing is which time zone converts the output value. There are several ways to control this:

When a time stamp with a time zone is displayed, it is always converted from UTC to the current zone of the time zone and displayed as local time in this zone. To see the time in another time zone, change the time zone or use the AT TIME ZONE construct (see Section 9.9.3 ).

Answer 3

You can make your choice using the at time zone statement:

 select insertTs at time zone 'CST' from table 

More details here .

Answer 4

I always store time in GMT so that the client can convert based on the current GMT offset (GMT offest is available in most languages).

I am writing C #, so I can easily convert all DateTime objects to GMT using DateTime.ToUniversalTime() , since I store the data in a database.

I’m not sure which language you use or how to convert GMT all the time to postgressql, but from a logical point of view, saving all times in GMT will create a single time zone, which all other time zones can easily refer to.

Hope this helps!

Greenwich Mean Time