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

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

From
avpro avpro
Date:
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;
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;

thanks for your hint,
John.

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

From
Andreas Kretschmer
Date:
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°


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

From
avpro avpro
Date:
i tried select '12:00'::interval + '9:00'::interval; and worked.

but, my fields are time with time zone, because the user needs to introduce the info in this format.

in this case, which is the correct syntax to find out how long the travel last between two entries, f1 and f2?

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

Johny

On 6 January 2014 20:38, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
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°


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice


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

From
Binand Sethumadhavan
Date:
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


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

From
Kevin Grittner
Date:
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


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

From
Tom Lane
Date:
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


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

From
avpro avpro
Date:
hi,

I'm using version 9.3.

sorry for this awkwardness; I'm not sure anymore what I've done.

select '12:00'::time - '9:00'::time;
is working;

however, I was looking to use the 'time with time zone' capability:
when I run:

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 **********

ERROR: operator does not exist: time with time zone - time with time zone
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Character: 30

how this works? where I could find more documentation?
what about the Cast aliases? where could I find them?

thanks once more for your input




On 7 January 2014 17:16, Kevin Grittner <kgrittn@ymail.com> wrote:
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

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

From
David Johnston
Date:
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.