brianc
Mismatch in Date precision causes odd behavior
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.
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
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.
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.
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
Added this to the docs milestone - I'll include a note around this when I discuss the type parsing there.
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.)
(And this did get added to the docs, at the bottom of https://node-postgres.com/features/types/.)
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.
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
