Postgres Timestamp with Time Zone

Question

I have a column "event_date_time" in my table "events" with type "timestamp with timezone". The python flask application uses the save date, for example, '2014-08-30 02:17:02+00:00' , but postgres will automatically convert it to '2014-08-30 07:17:02+05' . It converts the timestamp into my local timezone ie Pakistan. I want to save it without conversion. I tried

set time zone = 'UTC'

and this changes the time zone to "UTC", but pgadmin3 still saves the converted time.

I am using MAC OS and Postgresql 9.3.

Answer 1

The reason pgadmin shows the +5 clock is because the time zone of your system is set for this. When you save the timestamp with a time zone in GMT + or - any value, the system shifts any time zone to which you entered GMT (or UTC), so when you go to receive it, you can specify the time zone which you want it displayed.

For example, you can set the current time to say ... New York.

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

At the time of the request, he returned '2014-08-23 08: 50: 57.136817'. 8:50 Saturday morning, or 8:51 if you are pedantic.

Now, if we take the 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 you have a new time "2014-08-23 12: 50: 57.136817" ... 5 hours in the "future"!

Finally, let’s get the original timestamp and display it in what I consider the time zone in Pakistan (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';

Result? '2014-08-23 17: 50: 57.136817' even further into the future!

Again, I must emphasize the reason why he can do this, because he always converts the input time offset to UTC or GMT. In this way, Postgres processes all of its "timestamp with time zone" data types. It is designed to avoid problems with the time zone, such as daylight saving time, etc.

Your problem is that python inserts a time at an offset of +00, and if that should have been local time, you will have 5 hours from the postgres point. Not knowing exactly what requests python is doing, I would suggest that you can look at this to make sure it gives you the correct time, presumably set timezone='PKT' should be a fix. In any case, when you view the timestamp with a timezone using a browser such as pgadmin, the timestamp is converted to your timezone, which is why you see +5.

Alternatively, if you want to see these times at +00, you must indicate that you want this in SELECT queries.