CODEWITHSUNDEEP
postgresql
user636322
user636322

Reputation: 1201

Postgres convert integer into time

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?

Upvotes: 2

Views: 9400

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220842

Use INTERVAL arithmetic

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can just do:

select hh, ('00:00:00'::time + (hh - 1) * interval '30 minute') as time
from t;

Upvotes: 4

Related Questions