Node-postgres: 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 https://github.com/brianc/node-postgres/commit/b7fd9a5625e4571f24f59b555499317e70ea7f7c
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.

[CnApTaK]

Please, show me example, witch can change this behavior with config option.

[rpedela]

I am saying the solution might be to add a config option. That config option does not currently exist.

[spollack]

I like the idea of a config option for converting dates, particularly if it handles not just the timestamp without timezone datatype but also the date datatype too. see discussion here: https://github.com/brianc/node-postgres/pull/238

[CnApTaK]

And I like idea - to make an adequate driver. You can take any other driver working with Postgres - on request "SELECT NOW (), timezone ('UTC' :: text, now ())" you will get one of two options:

1. "2013-09-17 14:45:35.935528+00" and "2013-09-17 14:45:35.935528" (pgAdmin, Maestro)
2. "2013-09-17 17:45:35.935528+03" and "2013-09-17 14:45:35.935528" (php, java)
   Rather than to fix the bug in the driver, you shift it to the user. Nonsense.

[rpedela]

No need to be harsh.

The real issue from what I can tell is not the SQL date string which is all pgAdmin does. It is the conversion from a SQL date string to a Javascript date class. The Javascript date needs the time zone in order to convert to the right thing. I see two options:

1. Add config option for default time zone to node-postgres.
2. Read the server's default time zone config on connection with SELECT current_setting('TIMEZONE')

[brianc]

Hey @CnApTaK I'm sorry you're having a problem with the driver. I know it can be frustrating to expect software to work one way, and then have it do something different.

The good news is node-postgres comes with what we believe to be sane defaults for type parsing that should work for most people most of the time, but they're totally able to be changed by you. This way you can have your dates come out as UTC, or as strings, any other way you see fit.

If you want to override the date type parsing for dates without timezones you can do something like this to return the value as a string (which is how it is received within node-postgres from the backend server)

var types = require('pg').types;
var timestampOID = 1114;
types.setTypeParser(1114, function(stringValue) {
  return stringValue;
})

In this way you can completely modify or remove how node-postgres will translate dates into JavaScript objects. It we considered the existing behavior to be a _bug_ we would definitely fix it as quickly as we could, but as discussed above we don't. On this matter we'll have to agree to disagree, but thankfully we can all have node-postgres work exactly the way we want!

[zxiest]

IMHO, I think it's a bug, here's why:

when I do new Date("Wed Feb 11 2015 13:41:48") in javascript, it returns this date in MY TIMEZONE.

The datetime coming from postgres is in UTC, so let's say the time in the database is "Wed Feb 11 2015 13:41:48", if we get it in the code and put it in a new Date(), it gets converted to a wrong timezone. To do it correctly, we need to append the correct timezone to it (UTC).

Here's how I made use of your code, @brianc

pg = require('pg')
types = require('pg').types
timestampOID = 1114
types.setTypeParser 1114, (stringValue) ->
return new Date(Date.parse(stringValue + "+0000"))

Now this returns all my times correctly (offset to the right my computer's timezone so its .getTime() is correct)

I'd love to hear back from you guys =)

[jaysylvester]

I spent many hours banging my head against my keyboard trying to understand why the dates I was seeing in my app didn't correspond with the dates in the db. I can admit that it came down to my lack of understanding about JavaScript's date handling (applying the local time zone when no time zone is present in the timestamp) and I can understand the approach taken by node-postgres after reading through this thread. Still, the default date handling is unintuitive and confusing. I expect the timestamp in my app to match what's in the db (assumed UTC), not to have it converted to local time because that's JavaScript's weird way of dealing with it.

Using @zxiest's example above, I'm now getting proper UTC timestamps, but I was really scratching my head for a while.

No issues with node-postgres otherwise, though. Very fast and stable. Thanks for all your hard work.

[brianc]

:) My pleasure. We've gone back and forth and back and forth on date
handling. So we ended up settling on the way postgres recommends & then
providing an easy way to punch over for your own needs. Dates in general
are painful and sad and make me cry. I cannot recommend this lib enough if
you've not already used it: http://momentjs.com/

On Mon, Apr 20, 2015 at 2:31 PM, Jay Sylvester [email protected]
wrote:

I spent many hours banging my head against my keyboard trying to
understand why the dates I was seeing in my app didn't correspond with the
dates in the db. I can admit that it came down to my lack of understanding
about JavaScript's date handling (applying the local time zone when no time
zone is present in the timestamp) and I can understand the approach taken
by node-postgres after reading through this thread. Still, the default date
handling is unintuitive and confusing. I expect the timestamp in my app to
match what's in the db (assumed UTC), not to have it converted to local
time because that's JavaScript's weird way of dealing with it.

Using @zxiest https://github.com/zxiest's example above, I'm now
getting proper UTC timestamps, but I was really scratching my head for a
while.

No issues with node-postgres otherwise, though. Very fast and stable.
Thanks for all your hard work.

—
Reply to this email directly or view it on GitHub
https://github.com/brianc/node-postgres/issues/429#issuecomment-94531793
.

[jaysylvester]

I'm actually using Moment.js, which is an amazing library, but it was causing additional confusion because my dates were being double-converted. They'd come out of the db as UTC, get converted by node-postgres to local, then converted again because Moment thought they were still in UTC. It wasn't until I removed Moment from the chain that I saw what was happening. The date output overwrite function above combined with Moment is doing everything correctly now.

Would you consider adding a specific example to the docs regarding date handling? It's great that you have some customization docs in the wiki, but I think this is a case that could use its own section (unless it's already there and I missed it).

[brianc]

I actually tried to document using moment.js specifically here:

https://github.com/brianc/node-pg-types

[jaysylvester]

Cool, that's helpful, thanks. I see the link in the node-postgres readme that points to it, but completely missed it when I read the readme previously.

[joeltaylor]

I'm running into a similar issues that @jaysylvester shared with my dates in the DB being different than what is being output in the app. I was under the impression that as long as I stored dates with a time zone (UTC in this case) that when I retrieve them they won't be converted to local time. Or am I misunderstanding and the conversion to local time is the default behavior?

[brianc]

Are you storing your dates with the datatype TIMESTAMPTZ? That should
respect the timezone.

On Wed, May 20, 2015 at 2:28 PM, Joel Taylor [email protected]
wrote:

I'm running into a similar issues that @jaysylvester
https://github.com/jaysylvester shared with my dates in the DB being
different than what is being output in the app. I was under the impression
that as long as I stored dates with a time zone (UTC in this case) that
when I retrieve them they won't be converted to local time. Or am I
misunderstanding and the conversion to local time is the default behavior?

—
Reply to this email directly or view it on GitHub
https://github.com/brianc/node-postgres/issues/429#issuecomment-103987006
.

[joeltaylor]

Yup! I just wanted to make sure that the timezone should be respected. It must be a bug somewhere in my code. Thanks!

[onzag]

var types = require('pg').types; var timestampOID = 1114; types.setTypeParser(1114, function(stringValue) { return stringValue; })

I did that and I get segmentation fault.... feeling like C

[dannydulai]

pg.types.setTypeParser(1114, function(stringValue) {
    console.log(stringValue);
    return new Date(Date.parse(stringValue + "+0000"));
});

that worked perfectly for me.

[eduardobcastro]

Is 1114 a postgresql code or pg driver code? Is there any risk of this to change in future?

[dannydulai]

http://doxygen.postgresql.org/include_2catalog_2pg__type_8h.html

search for 1114 -- it the OID for timestamp in postgres itself

[ghost]

ok, thanks for the help given so far with this weird behavior. so I managed to read from DB the UTC times stored there. but now, I am sending UTC to DB and in DB I see local time not UTC (although the field in DB is type timestamp -- without timezone info and I do send UTC format, +0000 or GMT at the end of the string format: I send to DB something like this new Date('Wed, 23 Apr 2014 09:54:51 GMT') and when I check the DB value it is my local time.
how can I tell pg module to not convert to local time when sending/saving to DB?

[rpedela]

What is the output for SHOW TIMEZONE;?

[Globik]

By standard should be like this "2013-08-29T09:57:57.267863Z"

[JFFilip]

import pg, {types} from 'pg';
import moment from 'moment';

const TIMESTAMPTZ_OID = 1184;
const TIMESTAMP_OID = 1114;
const parseFn = (val) => {
return val === null ? null : moment(new Date(Date.parse(val + "+0000")));
};
types.setTypeParser(TIMESTAMPTZ_OID, parseFn);
types.setTypeParser(TIMESTAMP_OID, parseFn);

This is how it works for me + it return date as moment.js object

[dennislysenko]

Bump, but this behavior caused me a few issues that were pretty hard to track down on a project. What we ended up doing here was to override the driver to return strings instead of creating dates with an assumed timezone. The reason was, we were accessing dates from a db that was being written by a different microservice, which did not use timestamptz column types, but just timestamp. Our servers are in UTC, but when accessing a remote db from our local machines to test, the driver assumed localtime, which was incorrect.

It's sort of an edge case, but definitely not an unthinkable one. It illustrates why I don't think either of the solutions above - assuming UTC or assuming local time - are the best ones (just in my opinion). Here, assuming a time zone created issues that were hard to track down (we were creating an API with hourly data, and all the hours were shifted 8 hours into the future - this was not immediately obvious but caused our data to be incorrect because the dates _themselves_, coming out of the db query were incorrect).

A timestamp column without a timezone does not have a time zone, so it seems counterintuitive to me to have the driver assume a timezone which may or may not be correct, although I respect the convenience of directly outputting date objects, I unfortunately don't think Javascript's built-in Date object is good enough to justify being the primary data type output from queries: it's just too dangerous with too many pitfalls.

I think for non-tz timestamp columns, defaulting to a string/struct that explicitly has no timezone, and providing a few (documented) simple helper functions to create an ISO string, create a date object etc. from it while assuming a call-site specified timezone would be a bit more resilient, if more verbose.

Just my 2c.

[OBorce]

Dude come on, I have Date, why the hell would it try to convert to my timezone and get datetime with different date, how is this the correct behavior.

[charmander]

@OBorce It isn’t correct, it’s just backwards-compatible. Do this: https://github.com/brianc/node-postgres/issues/429#issuecomment-24870258