From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: how to add 'time with time zone' data types and 'interval' data types |
Date: | 2014-01-07 20:17:06 |
Message-ID: | 1389125826632-5785753.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Konoplev | 2014-01-07 22:44:58 | Re: Testing if a Column Exists in a NEW record of a Trigger Function |
Previous Message | avpro avpro | 2014-01-07 18:11:01 | Re: how to add 'time with time zone' data types and 'interval' data types |