How does PostgreSQL store datetime types internally

Question

When reading the PostreSQL (13) documentation, I came across this page, which lists the storage sizes of different datetime types.

Inter alia it states:

Name                         Storage Size       Description                                   Low Value      High Value      Resolution
----                         ------------       -----------                                   ---------      ----------      ----------
timestamp without time zone       8 bytes       both date and time (no time zone)               4713 BC       294276 AD   1 microsecond
timestamp with time zone          8 bytes       both date and time, with time zone              4713 BC       294276 AD   1 microsecond
time with time zone              12 bytes       time of day (no date), with time zone     00:00:00+1559   24:00:00-1559   1 microsecond

I can understand timestamp without time zone: There are roughly (4713 + 294276) * 365 * 24 * 60 * 60 * 1000000 microseconds between low and high value. (Not accounting for calender changes and such). This amounts to roughly 2^63 values and those can be stored in 8 bytes.

However, timestamp with timezone has the same range and resolution and can additonally store the time zone information. If we already use 63 bits for the values there is only one bit left, which cannot be enough to store the timezone, so the internal storage must work somehow different.

Even stranger, while timestamps use only 8 bytes, time with time zone needs 12 bytes, although it has the same resolution and a much smaller range of allowed values.

Thus my question: How does the internal storage of these types in PostgreSQL work?

This question can be answered by reading the source. (Postgres is open source) — wildplasser
– wildplasser, Commented Dec 4, 2020 at 17:05

Laurenz Albe · Accepted Answer · 2020-12-04 13:30:33Z

Both timestamp with time zone and timestamp without time zone are stored as an 8 byte integer representing microseconds since midnight on Jan 1 2000.

The difference is that timestamp with time zone is interpreted as being in UTC and is converted according to the current setting of the timezone parameter on display.

The definitions are in src/include/datatype/timestamp.h:

typedef int64 Timestamp;
typedef int64 TimestampTz;

time without time zone is a 8 byte integer representing microseconds since midnight (a 4 byte integer wouldn't suffice). See src/include/utils/date.h:

typedef int64 TimeADT;

time with time zone has an additional 4 byte integer representing the time zone offset in seconds.

See src/include/utils/date.h:

typedef struct
{
    TimeADT     time;           /* all time units other than months and years */
    int32       zone;           /* numeric time zone, in seconds */
} TimeTzADT;

Follow the documentation and avoid time with time zone:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness.

user330315 · Accepted Answer · 2020-12-04 12:54:38Z

4

and can additonally store the time zone information

No, it doesn't store time zone information.

From the same page you linked to

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

^{(emphasis mine)}

So timestamp and timestamptz are stored in the same. They just differ in the way the values are accepted from and returned to the client.

answered Dec 4, 2020 at 12:54

user330315

Collectives™ on Stack Overflow

How does PostgreSQL store datetime types internally

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related