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

avpro avpro <avprowebeden@gmail.com> wrote:

> hi all,
>
> 1st scenario: i have a table with two fields both of them ''time with time
> zone'. How could I add them? i would like to have something like: SELECT
> table.field1 + table.field2 as sum;

Add 2 TIME's? Make no sense.

test=*# select '9:00'::time + '12:00'::time;
ERROR:  operator is not unique: time without time zone + time without
time zone
LINE 1: select '9:00'::time + '12:00'::time;
                            ^
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.


What about to add 23:00 and 21:00? 44:00 as TIME?


> 2nd scenario, similar but this time data types are 'interval': i have a table
> with two fields both of them ''interval'. How could I add them? i would like to
> have something like: SELECT table.field1 + table.field2 as sum;

Just do it:

test=*# select '9:00'::interval + '12:00'::interval;
 ?column?
----------
 21:00:00
(1 row)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On 7 January 2014 20:58, avpro avpro <avprowebeden@gmail.com> wrote:
> select '12:00'::time - '9:00'::time; didn't work.

This ought to work, oughtn't it?

binand=# select '12:00'::time - '9:00'::time as duration;
 duration
----------
 03:00:00
(1 row)

Binand

avpro avpro <avprowebeden@gmail.com> wrote:

> select '12:00'::time - '9:00'::time; didn't work.

I get this on 9.3:

test=# select '12:00'::time - '9:00'::time;
 ?column?
----------
 03:00:00

(1 row)
What version are you using?  What happens when you try it?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner <kgrittn@ymail.com> writes:
> avpro avpro <avprowebeden@gmail.com> wrote:
>> select '12:00'::time - '9:00'::time; didn't work.

> I get this on 9.3:

> test=# select '12:00'::time - '9:00'::time;
> �?column?
> ----------
> �03:00:00

Note that that yields an interval, not a time.  I think the OP may
be unhappy about the type mismatch.  He could just add a cast,
though.  In the addition case, casting one or both of the times to
interval might do what he wants, too, depending on whether he's
looking for wraparound behavior:

regression=# select '12:00'::time + '19:00'::time::interval;
 ?column?
----------
 07:00:00
(1 row)

regression=# select '12:00'::time::interval + '19:00'::time::interval;
 ?column?
----------
 31:00:00
(1 row)

            regards, tom lane

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.