Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Mismatch in Date precision causes odd behavior #1200

Open
Carbonyte opened this issue Jan 19, 2017 · 10 comments
Open

Mismatch in Date precision causes odd behavior #1200

Carbonyte opened this issue Jan 19, 2017 · 10 comments
Milestone

Comments

@Carbonyte
Copy link

Carbonyte commented Jan 19, 2017

The PostgreSQL TIMESTAMP type has a maximum precision of 1 microsecond, but the JavaScript Date type only has a maximum precision of 1 millisecond. This causes information to be silently discarded, leading to bizarre behavior:

client.query("SELECT '2017-01-01 00:00:00.00001'::timestamp as d", (err, res) => {
let date = res.rows[0].d;

client.query("SELECT '2017-01-01 00:00:00.00001'::timestamp = $1 as eq,\
'2017-01-01 00:00:00.00001'::timestamp > $1 as gt", [date], (err, res) => {
console.log(res.rows[0].eq); //false
console.log(res.rows[0].gt); //true
});
});

I'm not sure this a bug that could be fixed without using a non-standard Date object, but even if it isn't fixed it should be at least noted in the documentation.

@vitaly-t
Copy link
Contributor

vitaly-t commented Jan 21, 2017

JavaScript doesn't support microseconds, so wouldn't this be self-implying for a JavaScript library?

No point documenting the obvious, imo.

And there is nothing bizarre in your example, because:

let date = res.rows[0].d;
//=> 2017-01-01T00:00:00.000Z

which makes the rest logically apparent:

2017-01-01 00:00:00.00001 = 2017-01-01T00:00:00.000Z => false
2017-01-01 00:00:00.00001 > 2017-01-01T00:00:00.000Z => true

@Carbonyte
Copy link
Author

Carbonyte commented Jan 22, 2017

The behavior isn't bizarre once you know the cause, but it isn't a particularly easy thing to debug if you don't. How the various Postgres datatypes map to JS types is itself not well documented, which is part of the problem.

@williamstein
Copy link

williamstein commented Feb 7, 2017

For what it's worth, I probably wasted about 60 hours last week, and cause a lot of pain to users, because there wasn't a sentence about this in the main README.md page. I did systematically read through the fine README before starting to use the driver, and if there was a warning about timestamps silently rounding by "textually deleting the last 3 digits" instead of rounding, it would have helped. (And other than this, I really like this driver!)

I think the behavior of the driver is perhaps more surprising than your examples above suggest. What happens with the driver is completely different than what happens using postgresql's ::timestamp(3) explicit cast, which rounds to the nearest timestamp, rather than just textually removing the last 3 digits.

@rpedela
Copy link
Contributor

rpedela commented Feb 21, 2017

A workaround is to override the type parser. That doesn't prevent confusion or fix the documentation, but at least you could handle microseconds with custom code.

var pg = require('pg');

function pgToString(value) {
    return value.toString();
}

pg.types.setTypeParser(1082, pgToString); // date
pg.types.setTypeParser(1083, pgToString); // time
pg.types.setTypeParser(1114, pgToString); // timestamp
pg.types.setTypeParser(1184, pgToString); // timestamptz
pg.types.setTypeParser(1266, pgToString); // timetz

@williamstein
Copy link

@rpedela THANKS!

@brianc brianc added this to the docs milestone May 24, 2017
@brianc
Copy link
Owner

brianc commented May 24, 2017

Added this to the docs milestone - I'll include a note around this when I discuss the type parsing there.

@charmander
Copy link
Collaborator

Other fun information: the binary parser exposes microseconds, but the text parser doesn’t, and the microseconds are ignored when serializing back. (I don’t think the binary parser should expose microseconds in this way, because the methods behave differently compared to the rest of the date API.)

Time is overall just really uncomfortable, but it might be okay to start producing frozen or carefully subclassed dates with a microsecond field for timestamptzs that can be round-tripped in a future major version? (For all other datetime-related types, parsing to Date as a default will be deprecated is the current idea.)

@charmander
Copy link
Collaborator

(And this did get added to the docs, at the bottom of https://node-postgres.com/features/types/.)

@salper
Copy link

salper commented Apr 14, 2020

The timestamp rounding has been fixed in https://github.com/bendrucker/postgres-date/releases/tag/v1.0.5. It will allow to consistently query for someDate::timestamp(3) for example.

@dko-slapdash
Copy link

dko-slapdash commented Aug 13, 2023

JFYI for future readers: ts::timestamptz(3) does rounding, and date_trunc('milliseconds', ts) does truncation (the truncation works the same way as postgres-date module used by node-postgres).

# select
  '2023-08-12 19:35:53.973605-07'::timestamptz, 
  '2023-08-12 19:35:53.973605-07'::timestamptz::timestamptz(3),
  date_trunc('milliseconds', '2023-08-12 19:35:53.973605-07'::timestamptz) \gx
-[ RECORD 1 ]------------------------------
timestamptz | 2023-08-12 19:35:53.973605-07
timestamptz | 2023-08-12 19:35:53.974-07
date_trunc  | 2023-08-12 19:35:53.973-07

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

8 participants