Adding <integer> seconds to a <timestamp> in PostgreSQL

Question

I have a table of items with the following columns:

start_time column (timestamp without time zone)
expiration_time_seconds column (integer)

For example, some values are:

SELECT start_time, expiration_time_seconds 
   FROM whatever 
       ORDER BY start_time;

         start_time         | expiration_time_seconds
----------------------------+-------------------------
 2014-08-05 08:23:32.428452 |                  172800
 2014-08-10 09:49:51.082456 |                    3600
 2014-08-13 13:03:56.980073 |                    3600
 2014-08-21 06:31:38.596451 |                    3600
 ...

How do I add the expiration time, given in seconds, to the start_time?

I have tried to format a time interval string for the interval command, but failed:

blah=> SELECT interval concat(to_char(3600, '9999'), ' seconds');
ERROR:  syntax error at or near "("
LINE 1: SELECT interval concat(to_char(3600, '9999'), ' seconds');

Vincent C. · Accepted Answer · 2020-04-14 15:36:11Z

113

The trick is to create a fixed interval and multiply it with the number of seconds in the column:

SELECT start_time, 
       expiration_time_seconds, 
       start_time + expiration_time_seconds * interval '1 second'
FROM whatever 
ORDER BY start_time;

        start_time          | expiration_time_seconds |          end_time
----------------------------|-------------------------|----------------------------
 2014-08-05 08:23:32.428452 |                  172800 | 2014-08-07 08:23:32.428452
 2014-08-10 09:49:51.082456 |                    3600 | 2014-08-10 10:49:51.082456
 2014-08-13 13:03:56.980073 |                    3600 | 2014-08-13 14:03:56.980073
 2014-08-21 06:31:38.596451 |                    3600 | 2014-08-21 07:31:38.596451

edited Apr 14, 2020 at 15:36

Vincent C.

9278 silver badges20 bronze badges

answered Feb 15, 2015 at 16:05

Adam Matan

138k155 gold badges414 silver badges585 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bonafernando Over a year ago

Thank you very much! Can you add space to start_time + expiration_time_seconds to make it easy to visualize?

Adam Matan Over a year ago

@bonafernando not sure I understand

bonafernando Over a year ago

I tried to edit his answer, but the amount of changes were so little I couldn't submit, then I added the comment above.

Rafe · Accepted Answer · 2023-12-31 11:44:24Z

3

As of version 9.5 it is possible to create interval with make_interval function like this:

make_interval(secs => 10)

also intervals can be added to timestamps easily with + operand:

start_time + make_interval(secs => 10)

it will add 10 seconds to start_time and return a new timestamp

answered Dec 31, 2023 at 11:44

Rafe

4291 gold badge6 silver badges16 bronze badges

Collectives™ on Stack Overflow

Adding <integer> seconds to a <timestamp> in PostgreSQL

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related