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:

1. if PR Fix handling of timestamp fields without timezone #237 goes in, we will end up with timestamps in all sorts of timezones in the database based on the local timezones of the clients, which breaks our design goal of all UTC in the db
2. its a substantial change (there is other non-node code that accesses the database, etc.)

I looked at overriding the timezone of the clients to be UTC, but there are issues with that:nodejs/node-v0.x-archive#3286

How do you propose we work around this?

Thanks,
Seth

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.

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.

@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 pg@0.12.3 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:
2013-02-14 13:49:32.579-08
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.

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.