Repository navigation
Mismatch in Date precision causes odd behavior #1200
Description
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.
Reacted by Behlül Uçar and jcalfee
Activity
vitaly-t commentedon 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:
which makes the rest logically apparent:
Carbonyte commentedon 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 commentedon 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 commentedon 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.
williamstein commentedon Feb 21, 2017
@rpedela THANKS!
brianc commentedon May 24, 2017
Added this to the docs milestone - I'll include a note around this when I discuss the type parsing there.
charmander commentedon Dec 6, 2019
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 toDateas a default will be deprecated is the current idea.)charmander commentedon Dec 6, 2019
(And this did get added to the docs, at the bottom of https://node-postgres.com/features/types/.)
salper commentedon 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 commentedon Aug 13, 2023
JFYI for future readers:
ts::timestamptz(3)does rounding, anddate_trunc('milliseconds', ts)does truncation (the truncation works the same way as postgres-date module used by node-postgres).