PostgreSQL timestamptz and timetz functions
I actually need to store local dates and times (aircraft with date and time) and utc offset, so I can convert to utc to calculate intervals (flight duration). People travel in local time and you need utc to calculate cross time zones. I was hoping to use timestamptz, but it absolutely doesn't work for this purpose. It converts everything to postgres timezone function. In my case, it is yyyy-dd-mm hh:mi:ss-07
.
However, I researched timetz only to cover all bases. It stores exactly what I need. It keeps the local time by providing the utc offset. Except now I need two columns instead of one to store information.
My questions are: Why do the timestamptz and timetz functions give different results? Is there a way to make timestamptz include the local timezone offset rather than the system timezone offset? Below are the queries to illustrate the difference:
select cast('2015-05-01 11:25:00 america/caracas' as timestamptz)
-- 2015-05-01 08:55:00-07
;
select cast('2015-05-01 11:25:00 america/caracas' as timetz)
-- 11:25:00-04:30
;
source to share
I personally find the wording timestamp with time zone
confusing when trying to understand PostgreSQL timestamptz
because it doesn't store the timezone. According to the docs:
All dates and times related to time are stored inside UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being shown to the client.
Note that on this page of storage characteristics and limits timestamp
and timestamptz
are identical.
In my head, to keep everything in order, I translated timestamptz
to "timestamp in the real world", and the usual timestamp
to "you probably didn't want to use this type" (because, I just discovered that I need to store timestamps, related to the real world.)
So:
Why do functions
timestamptz
andtimetz
give different results?
It looks like PostgreSQL didn't feel like they were allowed to work timetz
, for example timestamptz
:
The type is
time with time zone
defined by the SQL standard, but the definition shows properties that lead to questionable usefulness.
I am assuming that some of these "properties" are ones they don't like, and you do.
and
Is there a way to do it
timestamptz
include the local timezone offset and not the system timezone offset?
There is no offset value for values timestamptz
. These are just real world timestamps. So the way to store the local timezone is what you already thought of: store it separately.
create table my_table (
happened timestamptz,
local_time_zone varchar
);
select happened at time zone 'UTC', happened at time zone local_time_zone
from my_table;
source to share