1

I have two fields, one timestamp (calldate), and one bigint (duration - no idea why it's this big).

Through a query I would like to construct a 3rd field that is the timestamp plus the bigint as seconds.

With 9.3 I don't have access to make_interval, but the functions and operators seem useless.

select calldate + interval + duration || ' seconds' as "completion_date";
select calldate + interval + '900 seconds' as "completion_date";

The above is abbreviated a bit, but the 2nd line works while the 1st line fails. I'm unable to pass or construct the interval in a way it will accept. I was hoping to use the make_interval function that would return an interval type, but alas I'm using 9.3. I've tried many, many, different variations using () to try and isolate it as well as casting values.

Nothing works. How can I add the duration in seconds to a timestamp value?

4

To construct the interval, multiply the number with the 1 second interval:
duration * interval '1 second'

select calldate + duration * interval '1 second' as "completion_date";

or:

select calldate + duration * '1 second'::interval as "completion_date";

Postgres docs have a page about Date/Time Functions and Operators, where there are similar examples:

Operator    Example                     Result
  *         900 * interval '1 second'   interval '00:15:00'
  *         21 * interval '1 day'       interval '21 days'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.