13

In PostgreSQL, the data types timestamp and timestamp with timezone both use 8 bytes.

My questions are:

  1. What format is used to store date & time in a timestamp?
  2. How is the time zone information stored in the timestamp with timezone type, and how is it parsed later when reading the type?
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Eric
  • 22,183
  • 20
  • 145
  • 196

2 Answers2

26

This is just a misunderstanding stemming from the somewhat misleading type name. The time zone itself is not stored at all. It just acts as offset to compute a UTC timestamp (input), which is actually stored. Or as decorator in the display of a timestamp according to the current or given time zone (output). That's all according to the SQL standard.

Just the point in time is stored as UTC timestamp, no zone information. That's why 64 bit of information is enough. The timestamp is displayed to the client according to the current time zone setting of the session.

Details:

Also, since Jon mentioned it, time with time zone is defined in the SQL standard and thus implemented in Postgres. But it's an inherently ambiguous data type that cannot deal with DST reliably, hence its use is discouraged:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 3
    Yikes, that's *horribly* misleading. Thanks for correcting my misunderstanding. – Jon Skeet Mar 05 '15 at 11:17
  • @ErwinBrandstetter The new question is here: http://stackoverflow.com/questions/28877366/postgresql-query-timezone-db-server-client-machine-has-different-timezone – Eric Mar 05 '15 at 11:59
5

Looking at the documentation:

  • Timestamps are stored either as integers, or (deprecated) floating point numbers
  • I don't believe timestamp with timezone could be correctly encoded within 8 bytes if it actually stored a time zone. Just the timestamp requires 64 bits, as log2(298989 * 365 * 24 * 60 * 60 * 1000000) is greater than 63. Note that time with time zone requires 12 bytes, with the same precision but a range of a single day.

See Erwin's answer to explain how it actually manages to be stored in 8 bytes - it should be called "timestamp without a time zone, but stored in UTC and converted into the local time zone for display". Ick.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Hi, pg 9.3 doc chp 8.5 has a table, it shows `timestamp with timezone` use only 8bytes, because it ranges from `4713 BC` to `294276 AD`, so it don't need 8 bytes to store date & time, thus there are left bits for timezone info, but still wondering how the timezone is stored & parsed. – Eric Mar 05 '15 at 08:33
  • @Eric: I know it claims it only needs 8 bytes, but how do you expect it to cover that range of years down to microsecond precision in fewer than 8 bytes, ignoring the time zone? That was the point of the calculation in my answer - to show that 8 bytes are required for that range/precision combination. – Jon Skeet Mar 05 '15 at 08:35
  • I think the trick is at the range of value, the limited range reduce the bits it need to store values while still keep the precision, this is kind of math issue I guess, as your can see the `time with timezone` need 12 bytes, because it has a wider range. But that is not my concern :) I wish to understand the timezone store & parse part. – Eric Mar 05 '15 at 08:42
  • @EricWang: What "limited range" do you mean? 4713BC to 294276AD is 298989 years. How is that *possibly* a wider range than `time`, which is time within a *single day*, but to the same precision? In my world, 298989 years is a wider range than 1 day :) It would also help if you could explain *why* you need to know the precise storage format. Is this just curiosity? You should never need to deal with the raw storage format yourself. – Jon Skeet Mar 05 '15 at 08:57
  • My preview understanding about `time` type is not correct, compare it with `timezone` is not proper. After read pg doc & timestamp.c source code provided in question's comment, in c the timezone is a char or int value, later it will be compressed as less bits(enough to store 0~23), anyhow thanks for help. – Eric Mar 05 '15 at 09:29
  • @EricWang: Not sure what you mean by 0~23 - there are far more than 24 time zones in the world, and more than even just 24 UTC offsets. If you meant 24 bits, that's reasonable - but either way, there isn't even *1* bit spare to make `timestamp with time zone` realistically stored with the specified range and precision in 64 bits (8 bytes) - so it looks like the documentation is flawed. – Jon Skeet Mar 05 '15 at 09:32
  • 0~23 is my imagination, sorry for that :). It is a char type in c so up to 256 timezone maybe. I asked this question is because pg has a default timezone for timestamp without timezone, I want to get a better understand timezone to make sure I wrote correct program which would be used in multiple timezones. – Eric Mar 05 '15 at 09:38
  • 2
    The elephant in the room here is: the time zone is not stored at all. – Erwin Brandstetter Mar 05 '15 at 11:12