7

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.

Improve this question
5
  • 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
    – user1822
    Commented Mar 30, 2021 at 5:30
  • 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.
    – J. Choi
    Commented Mar 30, 2021 at 5:47
  • 1
    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.
    – user1822
    Commented Mar 30, 2021 at 5:54
  • 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...
    – J. Choi
    Commented Mar 30, 2021 at 6:08
  • 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
    – user1822
    Commented Mar 30, 2021 at 6:11

1 Answer 1

Reset to default
6

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.