Wrong timestamp!

[CnApTaK]

var pg = require('pg');
var conString = "...";
var client = new pg.Client(conString);
client.connect(function(err) {
if(err) {
return console.error('could not connect to postgres', err);
}
var sql = "SELECT NOW() as now1, timezone('UTC'::text, now()) as now2";
client.query(sql, function(err, result) {
if(err) {
return console.error('error running query', err);
}
console.log(result.rows);
client.end();
});
});

Result is:
[ { now1: Thu Aug 29 2013 11:37:14 GMT+0300 (FLE Summer Time),
now2: Thu Aug 29 2013 08:37:14 GMT+0300 (FLE Summer Time) } ]
And must be:
[ { now1: Thu Aug 29 2013 11:37:14 GMT+0300 (FLE Summer Time),
now2: Thu Aug 29 2013 08:37:14 GMT+0000 (UTC) } ]
or equal dates
This is tested with latest version node-postgres.

[CnApTaK]

With node-postgres version 0.8.4 resiult is:
[ { now1: Thu Aug 29 2013 12:43:33 GMT+0300 (EEST),
now2: Thu Aug 29 2013 12:43:33 GMT+0300 (EEST) } ]

[CnApTaK]

var parseDate = function(isoDate) {
console.log(isoDate)
shows:
2013-08-29 09:57:57.267863+00
2013-08-29 09:57:57.267863

[CnApTaK]

Why you made this changes?
//no timezone information
else {
return new Date(year, month, day, hour, min, seconds, mili);
}

[CnApTaK]

it's all because of a commit b7fd9a5
i don't understand the reason?

[rpedela]

Before it assumed that a timestamp missing a timezone had a UTC timezone and converted it. But that may not be true, and the data is modified without the application knowing. I think the current behavior is correct.

[brianc]

@rpedela thanks for helping on this one. Yeah @CnApTaK we went back on forth on this in the past, but this behavior is in line with how postgres behaves & most likely wont change again. Note: if for any reason this did change it would bump the major version & be considered breaking.

[CnApTaK]

speaking on the behavior of Postgres...
try this query in pgAdmin:
SELECT NOW() as now1, timezone('UTC'::text, now()) as now2
result is:
"2013-09-17 14:45:35.935528+00";"2013-09-17 14:45:35.935528"
Postgres behavior is absolutely correct, unlike the behavior of the driver

[brianc]

I'm not understanding you 100%, sorry.

If you use the timezone function and pass it a TIMESTAMPTZ data type, it removes the timezone information and returns a TIMESTAMP (without timezone). node-postgres has no way of knowing what timezone this is in as it contains no timezone information in the date stame, and therefore treats it as a local time.

What do you think it should do instead?

[CnApTaK]

it must interpret the data for zone UTC. otherwise how to get data from fields with type "timestamp without time zone"? if you read the data across multiple clients that have different time zones, and the data that they receive will be different

[brianc]

From the postgres docs:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.

I believe this is what came up before during the discussion. When you take a date out and it has no timezone attached, you convert it to local time of the client. The client can get the UTC time by doing something kinda like what's outlined here: http://stackoverflow.com/questions/948532/how-do-you-convert-a-javascript-date-to-utc

[CnApTaK]

so you are saying... when i need to work with 'timestamp without time zone' data, i need always use 'AT TIME ZONE' when i need read a value? and why pgAdmin read the value correctly without adding 'AT TIME ZONE' to the select query?

[rpedela]

Is your local timezone set to UTC?

[CnApTaK]

postgres server timezone set to UTC, pg clients timezones is different: +2, +3 etc

[rpedela]

Then in your case, timestamp without time zone is assumed to be UTC by Postgres. If you changed the local timezone to something else, then it would convert differently. Without reading your server config, node-postgres would not know that. Maybe a solution would be a config option for node-postgres to set the default time zone used for conversion in the case of timestamp without time zone. Although I think the default behavior is correct.