CODEWITHSUNDEEP
postgresqltimezone
Matt
Matt

Reputation: 4775

Specify a time zone to use as the reference time zone

Based on the first two answers, the question was unclear as originally posted, thus I am completely rewriting it:

The following question is concerned only with how and what data is stored, and is no way shape or form concerned with converting data upon retrieval. As such, converting at SELECT to the desired time zone is not an appropriate answer.

When inserting a value into a timestamp with time zone field, it is retrieved (and thus presumably stored) with the timestamp converted to the local time zone of the database at the time it was inserted.

That is so say, a 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 at the time of the insert was -05. Timestamps that were inserted during daylight savings time, when the database was at -04, are returned using the -04 time zone.

What I want is for all timestamps to use an arbitrary time zone when stored (and thus all be retrieved without any additional work as having that time zone). That is to say, were the server orbiting the planet, all times would be at +00:00 (arbitrary time zone), instead of -12:00 to +12:00.

Can I insert into a timestamp with time zone column such that all timestamps are stored relative to an arbitrary time zone? If so, how?

Original follows.

When inserting a value into a timestamp with time zone field, it is being converted to the server's current time zone.

Example: If I insert a value specifying a time zone of -1, retrieving it will give back the time at -5 (the time zone of the server at the time it was inserted).

Is it possible to specify that it should be stored using an arbitrary time zone?

Note: This question is not how to convert the returned time to another time zone, this is specific to how the time is stored.

Upvotes: 1

Views: 1471

Answers (4)

MattW
MattW

Reputation: 13212

I always store times in GMT so that the client can convert based on it's current GMT offset (the GMT offest is available in most language).

I write C# - so I can easily convert all DateTime objects to GMT using DateTime.ToUniversalTime() as I store data in the database.

I am not sure what language you are using or how to convert all times to GMT in postgressql, but from a logic standpoint - storing all times in GMT will allow a uniform time zone that all other time zones can easily relate to.

Hope this helps!

Greenwich Mean Time

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656231

You have to save the time zone offset in addition to the timestamp.

As @Milen already explained (and linked to the manual): a timestamp only saves a point in time (as abstract value). The time zone modifier is not saved, it only serves to adjust 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 special attention to the details of the AT TIME ZONE construct, and how I extract the time zone from the (local!) timestamp with time zone.
now() returns timestamp with time zone or timestamptz for short.

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

timestamp_at_origin displays the timestamp with time zone as seen at its origin. If I understood your question, then that is what you are looking for.
You could further improve formatting.

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

Upvotes: 1

Milen A. Radev
Milen A. Radev

Reputation: 62563

When inserting a value into a timestamp with time zone field what actually happens is the timestamp is converted to UTC. Another matter altogether is to what time zone that value is converted on output. There are a few ways to control that:

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

Upvotes: 1

Eric
Eric

Reputation: 95093

You can do your select with an at time zone operator:

select insertTs at time zone 'CST' from table

See more here.

Upvotes: 0

Related Questions