In case of MySQL,
TIMESTAMP values are stored as the number of seconds since the epoch (‘1970-01-01 00:00:00’ UTC)`
In case of PostgreSQL with the version less than or equal to 9.6
timestamp values are stored as seconds before or after midnight 2000-01-01
In case of PostgreSQL with the version greater than or equal to 10, there is no explanation about this
I have two questions about the internal logic of PostgreSQL.
- Does it still use the same standard as the version 9.6?
- Why "midnight 2000-01-01"? Unix epoch starts from
1970-01-01 00:00:00 UTC. J2000 epoch starts from
12 noon (midday) on January 1, 2000.
It seems like only a few systems use
Because PostgreSQL provides functions to convert UNIX epoch into the timestamp
to_timestamp or vice versa
EXTRACT(EPOCH FROM ...), using the standard that is different from UNIX epoch seems like to require additional offset calculations.