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

Question

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?

Is it something internally which affects performance or what?

What's really strange is that time with time zone is larger than timestamp with time zone. — nwellnhof
– nwellnhof, Commented Dec 7, 2015 at 16:45
@nwellnhof: I believe the difference is that "timestamp with time zone" is really badly named - it just means "I'll convert to UTC for you automatically" whereas "time with time zone" genuinely stores the UTC offset along with the time-of-day. Admittedly a UTC offset isn't the same as a time zone, but... — Jon Skeet
– Jon Skeet, Commented Dec 7, 2015 at 16:49
@JonSkeet You're right, the documentation says: "For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone." — nwellnhof
– nwellnhof, Commented Dec 7, 2015 at 17:01

nwellnhof · Accepted Answer · 2015-12-07 16:56:56Z

7

There are 86,400,000,000 microseconds in a day. That's more than 2³², 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 2³² and so can be stored in a 32-bit integer. (In fact it's less than 2³¹, which may make various things slightly simpler.)

edited Dec 7, 2015 at 16:56

nwellnhof

34.1k7 gold badges97 silver badges121 bronze badges

answered Dec 7, 2015 at 16:43

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anestis Kivranoglou Over a year ago

Yea that explains it. Although it would be helpful to provide a less precise format to reduce size because mostly in 98% of all use cases a precision of a second is more than enough.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related