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?
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";
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'