Results 1 to 4 of 4

Thread: add two date

  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: add two date

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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?

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by csckid View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •