Search code examples
sqldatabasepostgresqltimestamptimezone

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?

Solution

  • 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