Microseconds to date converter
Converts a sixteen-digit microsecond timestamp into a date. PostgreSQL stores timestamps at microsecond resolution internally, and Python’s `time.time_ns()` divided by a thousand gives the same.
How to use the microseconds to date converter
Microseconds show up most often when a value has come straight out of a database rather than through an application layer. PostgreSQL’s internal representation is microseconds since 2000-01-01, which is a different epoch again. A raw pg value needs the epoch shift as well as the unit change, and a value that decodes to 2053 is a Postgres-epoch number being read as a Unix one. Worth knowing that a JavaScript Date cannot hold microsecond precision at all: converting to one silently discards the last three digits, so a round trip through JavaScript loses resolution you may be relying on for ordering.
Questions
PostgreSQL internally, Python’s time functions, and anything derived from a high-resolution clock.
Postgres counts from 2000-01-01 internally, not 1970. A raw value needs the epoch shift too.
No. A Date is millisecond resolution, so converting silently drops the last three digits.
No, everything here is UTC. Timestamps are stored in UTC precisely so they are unambiguous; converting to a local zone is a separate step and needs to know the zone.
Sixteen for a current date. Ten is seconds, thirteen milliseconds, nineteen nanoseconds.