Convert specific time format to common one

All we need is an easy explanation of the problem, so here it is.

  • 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...

> OUTPUT:
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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply