CODEWITHSUNDEEP
postgresql
Anestis Kivranoglou
Anestis Kivranoglou

Reputation: 8184

PostgreSQL : Why "Time" type allocates the same size as "Timestamp"?

Reading about Postgresql data types and specifically about "Date/Time" types i noticed something weird (For me at least).

The "Time" data type allocates the same storage size (8 bytes) as the "Timestamp" type. Although "Time" is responsible of storing only the time while "Timestamp" is storing both Date and Time being a super-set of time.

In addition both types have the exact same precision (1 microsecond / 14 digits) leaving me questioning why they both allocate 8 bytes unlike the "Date" Type which allocates 4?

Date Time types

Is it something internally which affects performance or what?

Upvotes: 6

Views: 1282

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1502306

There are 86,400,000,000 microseconds in a day. That's more than 232, so the result can't be stored in 32 bits. The next best option is 64 bits, i.e. 8 bytes.

Compare that with the date type which covers 4713BC to 5874897AD, i.e. 5879611 years. That's around 2147483820 days, which is less than 232 and so can be stored in a 32-bit integer. (In fact it's less than 231, which may make various things slightly simpler.)

Upvotes: 6

Related Questions