Pg-promise: Inclusion of milliseconds in time formatting

[nkzawa]

It seems inserting Date to TIMESTAMP column loses the accuracy of milliseconds

db.none("INSERT INTO table (date) VALUES($1)", new Date());

Maybe it's because this lib formats Date using toUTCString.
(https://github.com/vitaly-t/pg-promise/blob/master/lib/formatting.js#L488)

Instead, we can use JSON.stringify() or #toISOString() to keep milliseconds ?

[vitaly-t]

UTC is the official default and recommended Date/Time presentation for PostgreSQL.

If you decide you don't like it, you can use any presentation you like, see Custom Type Formatting.

[nkzawa]

Thanks for your reply, but my point is about milliseconds, not about if it's UTC or not though.

[vitaly-t]

my point is about milliseconds

My answer precisely covers that. Use of Custom Type Formatting allows for any type of Date/Time presentation.

[nkzawa]

toISOString is also UTC, so I don't follow your answer. I understood we can change the default behaviour, but why you choose toUTCString and omit milliseconds as the default behaviour?

[vitaly-t]

why choose toUTCString

UTC is the official default and recommended Date/Time presentation for PostgreSQL.

and omit milliseconds

I do not omit anything, we use the standard UTC conversion provided by JavaScript.

[nkzawa]

toISOString is also UTC but with milliseconds though.
Actually I'm not sure if toUTCString is the only "standard" when you need UTC.

Anyway, thanks for your prompt answer!

[vitaly-t]

As I explained earlier, you can easily switch over to toISOString, if you like ;)

I'm not sure right now though if PostgreSQL will understand it by default or not.

[TazmanianD]

I just hit this as well and I'm going to push back and argue strongly this really is a bug. Just because the Javascript Date.toUTCString function doesn't include milliseconds doesn't mean a UTC time can't include milliseconds. The wiki article (https://en.wikipedia.org/wiki/Coordinated_Universal_Time) on UTC does make mention of milliseconds. The Date.UTC function accepts milliseconds as an argument and the Date object has a Date.getUTCMilliseconds function.

You say that Postgres uses UTC, which is correct, but that doesn't mean that Date.toUTCString is the correct formatting mechanism for it when sending dates from Javascript to Postgres. Postgres clearly supports milliseconds (https://www.postgresql.org/docs/9.1/static/datatype-datetime.html) (in fact it supports microseconds). You also said you don't know what formats Postgres supports that we could use as an alternative for toUTCString. The document says that Postgres is pretty lenient (and the ISO format is explicitly mentioned): "Date and time input is accepted in almost any reasonable format, including ISO 8601".

I've just moved from a project that used Java against Postgres to one that's using Javascript and I was quite surprised by the difference in behavior. With the Java libraries, when you pass a Date object, the database does indeed get and store the milliseconds. Furthermore, if you test with the base pg Node module, it does handle the dates with milliseconds. So you would have to argue that your module is correct and 'pg' is not.

Here's a specific test case:

const myDate = new Date('2016-11-08T08:55:15.804Z');

database.any('DROP TABLE IF EXISTS time_test')
  .then(() => {
    return database.any(`CREATE TABLE time_test (time TIMESTAMP WITH TIME ZONE)`);
  })
  .then(() => {
    return database.any(`INSERT INTO  time_test values ($1)`, myDate);
  })
  .then(() => {
    return database.any(`INSERT INTO  time_test values ($1)`, myDate.toISOString());
  })
  .then(() => {
    return database.any('SELECT * FROM  time_test');
  })
  .then((rows) => {
    for (const row of rows) {
      console.log(row.time);
    }
  });

This outputs:

2016-11-08T08:55:15.000Z
2016-11-08T08:55:15.804Z

You can see that when passing in a Date, the milliseconds are lost. When converting to an ISO string first, the milliseconds are preserved. So I think the toISOString is the correct mechanism here and not toUTCString.

I really really think this is a bug here.

[vitaly-t]

@TazmanianD

As stated previously, if you do not like the default Date transformation, you can easily set your own. Therefore, this won't escalate into a bug.

For that you can use Custom Type Formatting :wink:

[TazmanianD]

I obviously can't force you to change it but I do think this is a bug and your workaround really isn't the best solution. Your module is not behaving as I suspect most users would expect and it's not behaving like the pg module does either. Imagine someone who does an upgrade from pg to pg-promise and suddenly they find the different behavior. Having to modify the prototype for a built-in datatype isn't ideal either. I suppose we can just wait and see if anyone else finds this issue and votes on it but the more users who use this library, the more people any such change would affect since it wouldn't be backwards compatible.

[vitaly-t]

Imagine someone who does an upgrade from pg to pg-promise and suddenly they find the different behavior

node-postgres that you refer to has its own problems converting Date/Time properly. There is no ideal solution, per say, and what pg-promise uses is the recommended default for PostgreSQL.

You can change the query formatting easily, but changing the default now and breaking everyone's code isn't a good idea. The user base of this library is huge (over 300k downloads on npm this month), and I'd rather stay away from breaking things, unless really necessary.

[nkzawa]

I wonder when the change of the default behavior would actually break someone's code.
As SQL, 2016-11-16T06:25:38.182Z (toISOString) works fine as far as I tested.

[vitaly-t]

I would need to look into it in detail, this weekend perhaps ;)

In the meantime, just use Custom Type Formatting. It is simple, and it works.

[vitaly-t]

At the moment I'm considering to start forwarding Date/Time formatting into the driver.

See this commit where specific UTC flag was added.

[vitaly-t]

Implemented in release 5.4.1.