GitHub is home to over 20 million developers working together to host and review code, manage projects, and build software together.
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
Already on GitHub? Sign in to your account
Fields of type "timestamp without timezone" are still being parsed as if they had one.
Do not assume dates with no timezone specifier are UTC
NOTE: timestamp without timezone is oid 1114
@brianc @strk @cdauth sorry i'm late to the discussion here. I just updated to the latest pg and was broken by this change. I may be missing something here, but i feel like the original behavior prior to this change was far superior.
In our projects, we always store timestamps as UTC in the database, using the data type timestamp without timezone. The client code accessing the db may be running in any arbitrary timezone of course, and so we completely ignore timezones and just use Date.getTime() when comparing or otherwise manipulating timestamps, so everything is manipulated UTC as well. I think this is a fairly common pattern? However, with this change to pg, from a client with a non-UTC timezone if i store a timestamp and then read it back, i get a different value from getTime() because it has been shifted based on the local timezone. I do see the discussion in PR #237 (and I wasn't sure which thread to put this comment on... my apologies).
I do not want to change all the database types to timestamp with timezone in the schema for several reasons:
I looked at overriding the timezone of the clients to be UTC, but there are issues with that:joyent/node#3286
How do you propose we work around this?
If you can't have your application always run in UTC
(I think that'd be recommended) you could set your postgresql
session to be in UTC.
@spollack: I has the same issue as you, which is why I worked on #237. While I still think this should be fixed and will write the necessary tests for it as soon as I feel like doing that, I fixed the issue for myself by changing my database columns to TIMESTAMP WITH TIMEZONE after I realised that I had fundamentally misunderstood the way that column type works.
TIMESTAMP WITH TIMEZONE
The only difference between TIMESTAMP WITHOUT TIMEZONE and TIMESTAMP WITH TIMEZONE fields is that the former drops any timezone information from any dates, while the latter converts the times to UTC based on the timezone information in the date. So when you store the date 1970-01-01T01:00:00+01:00 into a field of the former type, it will be stored as 1970-01-01T01:00:00, and you will receive that when you select it again. If you store the date 1970-01-01T01:00:00+01:00 in a TIMESTAMP WITH TIMEZONE field, it will be converted to UTC, thus it will be saved as 1970-01-01T00:00:00. When you select it, it will be converted to the timezone of the client, so if a client running in timezone 05:00 selects it, they will receive 1970-01-01T05:00:00. This means that your assumtion 1 is incorrect, as those fields do not store the timezone that was specified during the insert, instead they convert the values to UTC.
TIMESTAMP WITHOUT TIMEZONE
@cdauth thanks, that is good news regarding how TIMESTAMP WITH TIMEZONE fields work -- i had the same misunderstanding, thanks for clarifying. Let me dig into that.
@cdauth i just tested this, and I don't see the behavior you describe.
I created a table with a TIMESTAMP WITH TIMEZONE field (postgres 9.2.x). I then used firstname.lastname@example.org to write a value to that field. My local timezone is PST. The value that ended up in the database (as viewed from either psql or pgadmin) is:
In other words, it wrote the value with the client local timezone, not in UTC.
not sure why we got different results?
I suppose because your Postgres client is set to the same timezone, so when displaying the value, it is converted back to your timezone. Try to SET TIMEZONE TO UTC in psql, the displayed values should change to UTC then.
SET TIMEZONE TO UTC
Thanks all. I set my default timezone in postgresql.conf to UTC, and verified that my sessions are are indeed in that timezone. After that, i do see TIMESTAMP WITH TIMEZONE fields in UTC via psql or pgadmin, like: 2013-02-14 23:37:02.923835+00
At this point, i can get the behavior i want if i switch to TIMESTAMP WITH TIMEZONE and also switch sessions to UTC. Doing only sessions in UTC but with TIMESTAMP WITHOUT TIMEZONE doesn't get the desired results.