How does PostgreSQL store timestamp internally?

Question

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.

1. Does it still use the same standard as the version 9.6?
2. 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 2000年01月01日 00:00:00.

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.

• user1822: Why do you care? As far as the conversion is concerned: you always have some offset calculation - only the value differs between the different implementations
• J. Choi: There is a possibilty of migrating the PostgreSQL DB to the MySQL one in my project. I was worried about their compatibility and found that they use different standards for the timestamp data. I just want to know if there is any advantage of using 2000 standard: if data are stored as UNIX epoch values, there would be no need of offset calculations for to_timestamp.
• user1822: Using epochs is a bad idea to begin with. But the timestamp 2021年03月30日 07:50:30 will always be the same thing, regardless on how the database stores it. Just make sure you transfer the data correctly during the downgrade.
• J. Choi: The link you provided really helped me much. I concerned about leap seconds but have to use BIGINT for the MySQL DB to avoid the 2038 problem. It provides me an additional reason to stay with PostgreSQL, which provides 8-byte timestamp field. However, the 2000 standard instead of the 1970 one remains with no sense to me...
• user1822: Shrug. As long as you us timestamp or timestamptz it really makes no difference how it's handled internally as long as the SQL expressions yield the correct value

Answer 1

That information from the 9.6 documentation is wrong. Timestamps are stored as microseconds since 2000年01月01日 00:00:00.

See include/datatype/timestamp.h:

/*
 * Timestamp represents absolute time.
 *
 * [...]
 *
 * Timestamps, as well as the h/m/s fields of intervals, are stored as
 * int64 values with units of microseconds. (Once upon a time they were
 * double values with units of seconds.)
 *
 * [...]
 */
typedef int64 Timestamp;
typedef int64 TimestampTz;

Back in 9.6, there was also the option to store timestamps as floating point values, but that has been removed since.

That should answer your first question.

The reason why 2000 was picked probably stems from the time when you could store timestamps as floating point values. Here, the precision got better for values close to the epoch, and it made sense to pick something more future-proof than 1970 (mind you, that was before 2000).

Subtracting a constant in order to calculate the Unix epoch is no great effort.

• wh81752: Laurenz Albe: isn't it the case that Postgres uses the Julian date (en.wikipedia.org/wiki/Julian_day) and counts microsecs from there? This would explain the lower timestamp limit of 4713 BC. IMHO, 4713BC is the real reference point.
• Laurenz Albe: No, it is as I wrote. It's just that PostgreSQL takes Julian day 0 as lower limit, out of tradition, as the source comments in src/include/datatype/timestamp.h say. Perhaps there are other reasons, like conversion from and to Julian dates would become difficult, but I don't know.