Re: how to add 'time with time zone' data types and 'interval' data types

avpro avpro wrote
> select '19:00:00+01'::timetz - '12:00:00+02'::timetz;
>
> i get:
>
> ERROR:  operator does not exist: time with time zone - time with time zone
> LINE 1: select '19:00:00+01'::timetz - '12:00:00+02'::timetz;
>                                      ^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> ********** Error **********

So you either need to add such an operator or, and likely the better
solution, convert the "timetz" into a "time" and then perform the math.

The naive way to do this would be:

SELECT '12:00 MST'::timetz::time

but the above simply drops the timezone specifier and leaves the time
unchanged (i.e., 12:00).  This is not a problem if both values are from the
same timezone but that should not be assumed.

The correct way is to normalize to a standard timezone (UTC makes sense to
me) first

SELECT timezone('UTC', '12:00 MST'::timetz)::time

Do this for both values then perform the math:

SELECT timezone('UTC','12:00 MST'::timetz)::time - timezone('UTC','15:00
EST'::timetz)::time

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-add-time-with-time-zone-data-types-and-interval-data-types-tp5785563p5785753.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.