add two date

csckid · #1 (**permalink**) 06-05-11, 09:50

how do you add two date in postgresql?
I tried this, but this gives an error

Code:

select finish_time + arrival_time from file_info

Code:

ERROR:  operator is not unique: time without time zone + time without time zone
LINE 1: select finish_time + arrival_time from file_info
                           ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Subtraction of two dates works

Code:

select finish_time - arrival_time from file_info

shammat · #2 (**permalink**) 06-05-11, 14:23

And what should the result of adding two dates be?

For subtracting this yields the difference in days.

What do you expect as the result of Sunday, June 5th plus Saturday, June 4th?

csckid · #3 (**permalink**) 07-07-11, 02:30

finish_time , arrival_time these are time without time zone.

finish_time "05:01:01"
arrival_time "03:02:02"

I would expect the answer to be 08:03:03

shammat · #4 (**permalink**) 07-07-11, 03:04

Quote:

Originally Posted by csckid

finish_time , arrival_time these are time without time zone.

finish_time "05:01:01"
arrival_time "03:02:02"

I would expect the answer to be 08:03:03

Sorry I didn't see that those were not columns of type date (mainly because you wrote "how can I add two dates"

)

Unfortunately you can only add an interval to a time value. But with a little trick you can "convert" the time into an interval:

Code:

SELECT finish_time + (interval '1' second) * extract(epoch from time arrival_time)
FROM your_table

This works as follows:

extract(epoch from time arrival_time) converts the time into seconds. Those seconds are then "converted" to an interval by multiplying a one second interval with them. The resulting interval can then be added to the date value.

Internet Services

Web Development

Digital Marketing

Consumer Tech