Menu
Reading about Postgresql data types and specifically about "Date/Time" types i noticed something wierd (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?
600
PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
Timestamp vs Timestamptz – What's the Difference? The big difference between these two data types is that timestamptz includes a timezone offset while timestamp does not. So it is important to decide for the timestamp you are storing whether the timezone is important to keep or not.
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.
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.)
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
