Postgres timestamp with timezone

Question

I have column 'event_date_time' in my 'events' table with type 'timestamp with timezone'. My python flask application is saving date like '2014-08-30 02:17:02+00:00' but postgres automatically converts it to '2014-08-30 07:17:02+05'. It converts the timestamp to my local timezone i-e Pakistan. I want to save it without converting. I have tried

set timezone='UTC'

and it does change timezone to 'UTC' but pgadmin3 is still saving the converted time.

I am using MAC OS and Postgresql 9.3.

Bangs

Do you mean that your select queries are returning the time at GMT+5 and you either want your python inserts to adhere to this time and not GMT+2, or you want to select at GMT+0? Either way, timestamp with time zone implicitly saves all entries to UTC and converts the time on select. – Bluestone

i think the field should be time stamp without timezone – Aghast

@Bluestone if timestamp with timezone implicitly saves all entries to UTC then why does pgadmin showing the time in GMT+5 ? – Bangs

@WingedPanther I tried time stamp without timezone and its working. Thanks. But shouldn't timestamp with timezone work also? – Bangs

Answer 1

The reason pgadmin is displaying hours +5 is because your system timezone is set to this. When you save a "timestamp with time zone" value at GMT + or - any value, the system offsets whatever timezone your input was to GMT (or UTC), so that when you go to retrieve it, you can specify the timezone you want it displayed in.

For example let's establish a current time for say... New York.

select now()::timestamp with time zone at time zone 'America/New_York';

At the time of asking it returned '2014-08-23 08:50:57.136817'. 8:50 Saturday morning, or 8:51 if you're being pedantic.

Now if we take that same time and display it in GMT we will see a different result:

select '2014-08-23 08:50:57.136817 America/New_York'::timestamp with time zone at time zone 'GMT';

Now have a new time of '2014-08-23 12:50:57.136817'... 5 hours into the "future"!

Finally let's get the original timestamp and display it in what I believe is the Pakistan time zone (PKT) and see what it shows

select '2014-08-23 08:50:57.136817 America/New_York'::timestamp with time zone at time zone 'PKT';

The result? '2014-08-23 17:50:57.136817' further into the future still!

Again I must stress the reason it can do this is because it is always converting the input time offset to UTC or GMT. Postgres processes all of its "timestamp with time zone" data types in this way. It is designed to avoid time zone problems such as daylight savings and so on.

Your issue appears to be that python is inserting the time at an offset of +00, and if this was supposed to be a local time then you will be 5 hours off as far as postgres is concerned. Without knowing exactly what queries python is making, I would assume you may want to look at that to make sure it is giving you the correct time, presumably set timezone='PKT' should be a fix. Either way, when you are viewing timestamp with time zone using a browser such as pgadmin, the timestamp is being converted to your local timezone and this is why you see +5.

Alternatively if you do wish to see those times at +00 then you must specify that you want this in your SELECT queries.

Bluestone

Answer 2

In postgresql both timestamp and timestamptz are always stored in UTC, both types have the same 64-bit size.

The timezone can be set in the current session/connection:

set timezone TO 'UTC';
set timezone TO 'America/New_York';
set timezone TO 'EST5EDT';
set timezone TO 'CEST-1CET';

Postgresql defines time zones using

• name - with daylight saving if used in a given country
• abbreviation - only with or without daylight saving
• POSIX definition - daylight saving can be defined arbitrarily

My rules for working with timestamp in postgres:

1. Set the UTC timezone in the session.
2. Use type timestamp in the UTC timezone to store. Don't use timestamptz
3. Use timezones definition according to POSIX or to name and use varchar(64) if need to store.

POSIX timezone format in Postgresql

• EST5EDT is the same as 'America/New_York'
• AAA5AAA,M3.2.0/2:00:00,M11.1.0/2:00:00 is the same as 'EST5EDT'
• CET-1CEST,M3.5.0,M10.5.0/3 is central european time with DST at full format

POSIX format description:

• EST5EDT and AAA5AAA - the number in the middle is the time offset relative to GMT, everything to the west is a positive number, everything to the east is a negative number (letters are most likely ignored, i don't know)
• ,.M3 third month (March)
• .2.0 second Sunday of the month (eg: .1.1 first Monday of the month)
• /2:00:00 time
• ,M11 eleventh month (November)
• .1.0 first Sunday of the month
• /2:00:00 is the time

Postgres timezone function

timestamp plus time offset EST5EDT

timezone('EST5EDT', timestamp) -- return type timestamptz

timestamptz minus time offset EST5EDT

timezone('EST5EDT', timestamptz) -- return type timestamp

Examples

Conversion timestamp to timestamptz

select
  'By name - America/New_York' as timezone_format,
  timezone('America/New_York', timestamp'2024-03-10 00:00:00') winter_dst,
  timezone('America/New_York', timestamp'2024-03-11 00:00:00') summer_dst
union all
select
  'by POSIX - EST5EDT' as timezone_format,
  timezone('EST5EDT', timestamp'2024-03-10 00:00:00') winter_dst,
  timezone('EST5EDT', timestamp'2024-03-11 00:00:00') summer_dst
union all
select
  'by POSIX full - AAA5AAA,M3...' as timezone_format,
  timezone('AAA5AAA,M3.2.0/2:00:00,M11.1.0/2:00:00', timestamp'2024-03-10 00:00:00') winter_dst,
  timezone('AAA5AAA,M3.2.0/2:00:00,M11.1.0/2:00:00', timestamp'2024-03-11 00:00:00') summer_dst;

Result:

set timezone to 'UTC'
+--------------------------+--------------------------+--------------------------+
|timezone_format           |winter_dst                |summer_dst                |
+--------------------------+--------------------------+--------------------------+
|By name - America/New_York|2024-03-10 05:00:00 +00:00|2024-03-11 04:00:00 +00:00|
|by POSIX - EST5EDT        |2024-03-10 05:00:00 +00:00|2024-03-11 04:00:00 +00:00|
|by POSIX full - AAA5AAA,M3|2024-03-10 05:00:00 +00:00|2024-03-11 04:00:00 +00:00|
+--------------------------+--------------------------+--------------------------+

set timezone to 'EST5EDT';
+--------------------------+--------------------------+--------------------------+
|timezone_format           |winter_dst                |summer_dst                |
+--------------------------+--------------------------+--------------------------+
|By name - America/New_York|2024-03-10 00:00:00 -05:00|2024-03-11 00:00:00 -04:00|
|by POSIX - EST5EDT        |2024-03-10 00:00:00 -05:00|2024-03-11 00:00:00 -04:00|
|by POSIX full - AAA5AAA,M3|2024-03-10 00:00:00 -05:00|2024-03-11 00:00:00 -04:00|
+--------------------------+--------------------------+--------------------------+

Conversion timestamptz to timestamp

select
  'by POSIX - use UTC' as timezone_format,
  timezone('EST5EDT', timestamptz'2024-03-10 00:00:00Z') winter_dst,
  timezone('EST5EDT', timestamptz'2024-03-11 00:00:00Z') summer_dst
union all
select
  'by POSIX - use session timezone' as timezone_format,
  timezone('EST5EDT', timestamptz'2024-03-10 00:00:00') winter_dst,
  timezone('EST5EDT', timestamptz'2024-03-11 00:00:00') summer_dst;

set timezone TO 'UTC';
+-------------------------------+-------------------+-------------------+
|timezone_format                |winter_dst         |summer_dst         |
+-------------------------------+-------------------+-------------------+
|by POSIX - use UTC             |2024-03-09 19:00:00|2024-03-10 20:00:00|
|by POSIX - use session timezone|2024-03-09 19:00:00|2024-03-10 20:00:00|
+-------------------------------+-------------------+-------------------+

set timezone TO 'EST5EDT';     
+-------------------------------+-------------------+-------------------+
|timezone_format                |winter_dst         |summer_dst         |
+-------------------------------+-------------------+-------------------+
|by POSIX - use UTC             |2024-03-09 19:00:00|2024-03-10 20:00:00|
|by POSIX - use session timezone|2024-03-10 00:00:00|2024-03-11 00:00:00|
+-------------------------------+-------------------+-------------------+

Bussy