CODEWITHSUNDEEP
sqldatabasepostgresqltimestamptimezone
Aleksa Milenkovic
Aleksa Milenkovic

Reputation: 93

Is there a way that i store timestamp with time zone in Postgres and not converting it to UTC

I want to save in Postgres TIMESTAMP WITH TIME ZONE column

2012-08-24 14:00:00+03:00

After a SELECT I am getting:

2012-08-24 11:00:00+00:00

I know that my DB is on UTC zone, but is there a way to save it and not lose original timezone info that I sent?

Upvotes: 9

Views: 5233

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 339837

tl;dr

Postgres adjusts all TIMESTAMP WITH TIME ZONE inputs to UTC, an offset of zero. Retrieved values remain in UTC, an offset of zero.

If you need the original offset, record that info in another column.

Offset versus Zone

To clarify terms…

  • An offset is merely a number of hours-minutes-seconds ahead or behind the temporal meridian of UTC. That meridian is basically where the sun at noon is directly overhead the Royal Observatory, Greenwich.
  • A time zone is much more. A time zone is a named history of the past, present, and future changes to the offset used by the people of a particular region as decided by their politicians. Real time zones have a name in Continent/Region format such as Europe/Paris, Africa/Tunis, Pacific/Auckland.

Postgres adjusts to UTC (offset of zero)

timestamp with timezone column'2012-08-24 14:00:00+03:00' and after trying to do a select i'm getting '2012-08-24 11:00:00+00

As you have seen, and as documented, for a column of type TIMESTAMP WITH TIME ZONE, Postgres uses the input’s offset-from-UTC to adjust to an offset of zero hours-minutes-seconds.

Your offset indicates a time of 14:00 with an offset of 3 hours ahead of UTC. So Postgres adjusts the time to 11:00 with an offset of zero. Same moment, same point on the timeline, different perspective.

Beware of tooling with auto-adjust anti-feature

Postgres retrieves the stored value with its offset of zero.

Unfortunately, many tools choose to dynamically apply some default time zone onto the retrieved value. While well-intentioned, this anti-feature confuses the picture, creates an illusion that the moment was stored with a particular time zone or offset. But, no, in Postgres values in a TIMESTAMP WITH TIME ZONE are always stored in UTC (offset of zero), and always retrieved in UTC (offset of zero).

Behavior varies across database engines

The SQL standard barely addresses date-time issues. So much of date-time handling behavior in various databases is implementation-specific.

Some other database engines do the same as Postgres, auto-adjusting to an offset of zero. But some database engines may not. Always study the doc.

Store offset in another column

If knowing the original offset is important to you, you’ll need to save that fact as a value in another column. I would suggest a column of a textual type, storing offset values in standard ISO 8601 format.

Similarly, if remembering the original time zone is important to you, record the time zone name in a second column separate from the TIMESTAMP WITH TIME ZONE column.

Upvotes: 8

Zegarek
Zegarek

Reputation: 26347

No date/time type stores time zone because it's a separate piece of information. Do not use plain timestamp if your source data comes with timezone offsets because it'll be trimmed off - the timestamptz is just as light, just as flexible, and it doesn't truncate the offset. If you wish to retain the source offset/timezone, you need to save it to a separate column - timestamp is meant to store a when not a where - the latter is only used to clarify the former.

If offset or other valid time zone info is present in the value literal/constant, it's used to shift the timestamp to UTC for internal storage. When the db reads it back to you when you select, it's shifted once again according to your timezone setting: demo at db<>fiddle

create table test(tstz timestamptz, ts timestamp);
insert into test 
select '2012-08-24 14:00:00+03:00'::timestamptz,
       '2012-08-24 14:00:00+03:00'::timestamp
returning *;
tstz ts
2012-08-24 11:00:00+00 2012-08-24 14:00:00

The offset you see by default when you select a timestamptz is your current timezone: it basically means this timestamp, as observed in a timezone with this offset. Note that unless you add the minutes :00, it's trimmed off as insignificant both in default output and in to_char() formatting function. If you really want to get the output you specified, for a reason only known to you, by all means you can - simply set the setting accordingly:

set timezone='utc-03:00';
select tstz,ts from test;
tstz ts
2012-08-24 14:00:00+03 2012-08-24 14:00:00 
select to_char(tstz,'YYYY-MM-DD HH-MI-SSAMOF'),
       to_char(ts,'YYYY-MM-DD HH-MI-SSAMOF') from test;
to_char to_char
2012-08-24 02-00-00PM+03 2012-08-24 02-00-00PM+00 
--Standard time: Australian Central Western Standard Time (ACWST)
--Example city: Eucla
set timezone='UTC +8:45';
select tstz,ts from test;
tstz ts
2012-08-24 02:15:00-08:45 2012-08-24 14:00:00 
select to_char(tstz,'YYYY-MM-DD HH-MI-SSAMOF'),
       to_char(ts,'YYYY-MM-DD HH-MI-SSAMOF') from test;
to_char to_char
2012-08-24 02-15-00AM-08:45 2012-08-24 02-00-00PM+00

Upvotes: 7

Related Questions