I need to convert the following (hh)integer column into a time column. Expected results below:
hh time
1 00:00
2 00:30
3 01:00
4 01:30
...
48 23:30
Can you help?
2 Answers
You can just do:
select hh, ('00:00:00'::time + (hh - 1) * interval '30 minute') as time
from t;
Comments
SELECT
TIME '00:00' + (1 - 1) * INTERVAL '30 minutes',
TIME '00:00' + (2 - 1) * INTERVAL '30 minutes',
TIME '00:00' + (3 - 1) * INTERVAL '30 minutes',
TIME '00:00' + (4 - 1) * INTERVAL '30 minutes',
TIME '00:00' + (5 - 1) * INTERVAL '30 minutes',
-- ...
TIME '00:00' + (48 - 1) * INTERVAL '30 minutes'
Another solution would be to work with unix timestamps:
SELECT
(to_timestamp((1 - 1) * 30 * 60) at time zone 'UTC')::time,
(to_timestamp((2 - 1) * 30 * 60) at time zone 'UTC')::time,
(to_timestamp((3 - 1) * 30 * 60) at time zone 'UTC')::time,
(to_timestamp((4 - 1) * 30 * 60) at time zone 'UTC')::time,
(to_timestamp((5 - 1) * 30 * 60) at time zone 'UTC')::time,
-- ...
(to_timestamp((48 - 1) * 30 * 60) at time zone 'UTC')::time
select (make_interval(mins := (hh-1)*30))::time;