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

Question

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?

Alarm

Not if you are using timestamptz type. If you want to do this then you need to use the timestamp type for the timestamp value and a separate column for the offset/timezone . – Mccartney

That is my alternative, but there's no way to do this without a separate column ? @AdrianKlaver – Alarm

No. timestamptz does not store the time zone it just rotates the timestamp to a UTC value for storage. It then rotates the value from UTC to whatever is the controlling timezone value is at the time. See 8.5.1.3. Time Stamps here Datetime Types for more information. – Mccartney

You can and should use timestamptz if your source data comes with timezone offsets. – Barcot

Answer 1

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

Barcot

Answer 2

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.

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.

Craftsman