Menu
Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONE versus WITHOUT TIME ZONE? Can the differences be illustrated with simple test cases?
332
The difference arises from what the system can reasonably know about the value: With a time zone as part of the value, the value can be rendered as a local time in the client. Without a time zone as part of the value, the obvious default time zone is UTC, so it is rendered for that time zone.
The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.
To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
The TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) data type stores 8-byte date values that include timestamp and time zone information in UTC format. You cannot define a TIMESTAMPTZ column with a specific precision for fractional seconds other than 6.
The differences are covered at the PostgreSQL documentation for date/time types. Yes, the treatment of TIME or TIMESTAMP differs between one WITH TIME ZONE or WITHOUT TIME ZONE. It doesn't affect how the values are stored; it affects how they are interpreted.
The effects of time zones on these data types is covered specifically in the docs. The difference arises from what the system can reasonably know about the value:
With a time zone as part of the value, the value can be rendered as a local time in the client.
Without a time zone as part of the value, the obvious default time zone is UTC, so it is rendered for that time zone.
The behaviour differs depending on at least three factors:
WITH TIME ZONE or WITHOUT TIME ZONE) of the value.Here are examples covering the combinations of those factors:
foo=> SET TIMEZONE TO 'Japan'; SET foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 00:00:00+09 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 06:00:00+09 (1 row) foo=> SET TIMEZONE TO 'Australia/Melbourne'; SET foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 00:00:00+11 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP; timestamp --------------------- 2011-01-01 00:00:00 (1 row) foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE; timestamptz ------------------------ 2011-01-01 08:00:00+11 (1 row) 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
