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.

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.