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

1 answer

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.)


Why do functions timestamptz

and timetz

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.


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;




All Articles