Postgres timestamp with timezone

postgresql pgadmin postgresql-9.3
Waseem picture Waseem · Aug 23, 2014 · Viewed 25.6k times · Source

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

Lucas picture Lucas · Aug 23, 2014

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.

Related questions

Postgresql: password authentication failed for user "postgres"

I have installed PostgreSQL 8.4, Postgres client and Pgadmin 3. Authentication failed for user "postgres" for both console client and Pgadmin. I have typed user as "postgres" and password "postgres", because it worked before. But now authentication is failed. I did it …

Read More
How connect Postgres to localhost server using pgAdmin on Ubuntu?

I installed Postgres with this command sudo apt-get install postgresql postgresql-client postgresql-contrib libpq-dev Using psql --version on terminal I get psql (PostgreSQL) 9.3.4 then I installed pgadmin with sudo apt-get install pgadmin3 Later I opened the UI and create the server …

Read More
Export and import table dump (.sql) using pgAdmin

I have pgAdmin version 1.16.1 So, for exporting table dumm I do: Right click on table, then in menu click on backup, then in Format choice Plain and save file as some_name.sql Then I remove table. Ok, now I …

Read More