• Environment:

I have 'duration' datas stored as varchar(4) in a table. 2 first characters are hours, 2 last characters are minutes. E.g.:

0100 means 01:00
0456 means 04:56

and so on...

  • What I need:

I have to make a query that retrieve this field as a common time format : HH:MM

  • What I have tried:

No real clue at the moment. I tried to cast my value as interval, but it doesn't behave as expected

> SELECT time_field, time_field::interval FROM myTable...

0100    |    01:40
0120    |    02:00
0179    |    02:59

I would like to avoid use of substring to add ':' in between 2 characters pairs.

Any help appreciated. Thanks.


You can first create a timestamp and then cast that to a time value:

select time_field, to_timestamp(time_field, 'hh24mi')::time
from the_table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.