How to add seconds to a timestamp to get an ending timestamp?

Command Prompt is one of the oldest Postgres support companies in the world and we have been blessed to be extremely busy with Professional community development including meetups and conferences. With the current climate we thought it would also be useful to remind people of “Simple Tips”. Simple tips will help new users of Postgres and related technologies to answer those pesky little questions that are in the back of their heads about how to do “insert idea here”.

How to add seconds to a timestamp to get an ending timestamp?

Assume you have have 2 values:

starting timestamp

duration (seconds)

How do you determine the ending time? 

 

There are a number of ways this can be done and here are two:

 

  • The first one is to use an interval as your second column.

 

CREATE TABLE time_test (one TIMESTAMP, duration INTERVAL);
INSERT INTO time_test VALUES (now(), '10 seconds');
SELECT one, one + duration AS duration FROM time_test;



            one             | duration          

----------------------------+----------------------------

 2020-04-08 10:03:34.806522 | 2020-04-08 10:03:44.806522

 

  • The second option is to use the function make_interval():

 

CREATE TABLE time_test_2(one TIMESTAMP, duration INTEGER);
INSERT INTO time_test_2 VALUES(now(), 10);
SELECT one, one + make_interval(secs => duration) AS duration FROM time_test_2;

            one             | duration          

----------------------------+----------------------------

 2020-04-09 13:51:53.703337 | 2020-04-09 13:52:03.703337

 

This is just the first of a series of simple tips that we will be delivering to make your life easier with Postgres!