Timestamp with time zone


Please can you explain the meaning of this sentence!

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instance in UTC, regardless of the TIME ZONE offsets stored in the data. For example

  TIMESTAMP '1999-04-15 8:00:00 -8:00'

is same as the

  TIMESTAMP '1999-04-15 11:00:00 -5:00'

Many thanks!

Best Answer

Firstly, read the Wikipedia article that explains UTC/GMT.

Time stamps with time zones are expressed as a date, a local time (eg: 08:00:00, which is 8am), and an offset (eg: -8, which would be 8 hours behind UTC). To convert a local time into UTC you use the offset.

So, in your example of:

TIMESTAMP '1999-04-15 8:00:00 -8:00'

... the local time is 08:00:00, which is 16:00:00 UTC (08:00:00 + 8 hours, because it is 8 hours behind UTC).

TIMESTAMP '1999-04-15 11:00:00 -5:00'

... the local time is 11:00:00, which is 16:00:00 UTC (11:00:00 + 5 hours, because it is 5 hours behind UTC).

The two times and dates in your example are identical when converted to UTC - that is what the sentence in your question means.