All we need is an easy explanation of the problem, so here it is.
I have ‘duration’ datas stored as varchar(4) in a table.
2 first characters are
hours, 2 last characters are
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 :
- 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.
You can first create a
timestamp and then cast that to a
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 🙂