Storing hours, minutes and seconds effectively

Question

Using a PostgreSQL database, what is the best way to store time, in hours, minutes and seconds. E.g. "40:21" as in 40 minutes and 21 seconds.

Example data:

   20:21
 1:20:02
12:20:02
   40:21

Answer 1

time would be the obvious candidate to store time as you describe it. It enforces the range of daily time (00:00:00 to 24:00:00) and occupies 8 bytes.

interval allows arbitrary intervals, even negative ones, or even a mix of positive and negative ones like '1 month - 3 seconds' - doesn't fit your description well - and occupies 16 bytes. See:

• How to get the number of days in a month?

To optimize storage size, make it an integer (4 bytes) signifying seconds. To convert time back and forth:

SELECT EXTRACT(epoch FROM time '18:55:28');  -- 68128 (int)    
SELECT time '00:00:01' * 68128;              -- '18:55:28' (time)

Answer 2

It sounds like you want to store a length of time, or interval. PostgreSQL has a special interval type to store a length of time, e.g.

SELECT interval'2 hours 3 minutes 20 seconds';

This can be added to a timestamp in order to form a new timestamp, or multiplied (so that (2 * interval'2 hours') = interval'4 hours'. The interval type seems to tailor-made for your use case.