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

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    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.

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

Back To Top
Search