postgres timestamp precision vs javascript timestamp precision

[uladkasach]

node.js Date() -> precision of milliseconds

postgres timestamps -> precision of microseconds

and when we're saying "record.created_at <= ${record.createdAt.toISOString()}" we are loosing the extra precision

• so assume:
  • record.created_at = 12:30:21.123456
• then
  • record.createdAt => 12:30:21.123
  • => record.created_at <= record.createdAt => 12:30:21.123456 <= 12:30:21.123000 => false

this is a non intuitive bug that will crop up over and over due to the difference in precision.

our options are to:

• store data in the rounded precision [loss-of-information]
• round the precision when reading [poor-performance]
• remember this bug each time we want to compare timestamps from js to sql [not-maintainable]

[uladkasach]

we can not use the "loss-of-information" option because for high speed writes we do need the created_at to maintain a microsecond precision in order for version information not to be lost

so that option is out, leaving us with only:

• round the precision when reading [poor-performance]
• remember this bug each time we want to compare timestamps from js to sql [not-maintainable]

[uladkasach]

i guess the other option is to require usage of a more precise date class:

• e.g., PreciseDate or Timestamp
  • maybe domain-objects can come with that one
  • it has defaults in which:
    • it is always a string
    • it can be parsed as iso
    • it has default precision of 6 (microseconds)

and then the sql-dao-generator can just ban using Date as the constructor, because it causes this bug

[uladkasach]

would date-fns work with that option though? ^

[uladkasach]

looks like it would not: https://www.reddit.com/r/javascript/comments/ff76xr/askjs_are_there_any_js_datetime_formatting/

may need to create a library that lets javascript work with microsecond precision timestamps :thinking:

maybe we can extend the normal Date fn w/ an added field of microseconds :thinking:

or maybe we can wrap that rust package like that thread recommended

[uladkasach]

:tada: - instead of making our own - we should be able to use Temporal when it comes out: https://tc39.es/proposal-temporal/docs/index.html

https://github.com/tc39/proposal-temporal