PostgreSQL timestamptz and timetz functions

Question

I really need to store local date and time (aircraft wheels up date and time) and the utc offset so I can convert to utc to calculate intervals (flight duration). People travel on local time and you need utc for cross time zone computations. I had hoped to use timestamptz but it absolutely does not work for this purpose. It converts everything to a function of the postgres time zone. In my case this is yyyy-dd-mm hh:mi:ss-07.

However, I investigated timetz just to cover all the bases. It stores exactly what I need. It preserves the local time while providing the offset to utc. Except that now I need two columns instead of one to store the information.

My questions are: Why do the timestamptz and the timetz functions give different results? Is there a way to make the timestamptz include the local time zone offset rather than the system time zone offset? Here are the queries that 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
;

Answer 1

I personally find the wording timestamp with time zone confusing when trying to understand PostgreSQL's timestamptz, because it doesn't store any time zone. According to the docs:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

Notice, on that page, that the storage characteristics and limits of timestamp and timestamptz are identical.

In my head, to keep things straight, I translate timestamptz to "timestamp in the real world", and plain timestamp to "you probably didn't mean to use this type" (because, so far, I've only found myself needing to store timestamps that are related to the real world.)

So:

Why do the timestamptz and the timetz functions give different results?

It appears to be because PostgreSQL didn't feel they were allowed to make timetz work like timestamptz does:

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

My guess is that some of these "properties" are the ones that they don't like, and you do.

And:

Is there a way to make the timestamptz include the local time zone offset rather than the system time zone offset?

There's no offset being stored for timestamptz values. They're just real-world timestamps. So the way to store the local time zone is the way you've 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;