php - Postgres Duration Storage

Question

I would like to store a duration in database, in my case it's a training duration (sport). We set when we do the training (datetime) and how long the session is (duration).

At the moment, I've choose the Time type, then I've: 01:00:00 for an hour of training, and retrieve a Datetime object in PHP (but I think it's not ideal to sum them for exemple) and in my form I've an undesired behavior: the html time type automatically set the current hour when the time is empty.

I hesitate in:

• keep time, it seem logical/simple to have something like: 00:00:00
• store it as secondes or minutes as an integer, maybe simpler to do sum of training amount for exemple (need to create a converter to display it as 00:00 or 00:00:00, and find a nice way to fill it in form)
• a way I've not think ?

Thanks a lot for your help.

question from:https://stackoverflow.com/questions/65883527/postgres-duration-storage

Answer 1

The correct data type to store a duration is interval.

This makes it easy to do date arithmetic: you can simply add the duration to the start time to get the end time:

SELECT INTERVAL '01:30:00';

 interval 
----------
 01:30:00
(1 row)

SELECT TIME '09:00:00' + INTERVAL '01:30:00';

 ?column? 
----------
 10:30:00
(1 row)

SELECT TIMESTAMP WITH TIME ZONE '2021-03-28 02:00:00+01' + INTERVAL '01:30:00';

        ?column?        
------------------------
 2021-03-28 04:30:00+02
(1 row)