CODEWITHSUNDEEP
sqlpostgresqltimecasting
Kyle H
Kyle H

Reputation: 3303

Postgres - How to cast varchar to time with just minutes and seconds?

I have a column that has data in the format of MI:SS. I need to convert this to a TIME data type, but when trying to cast to TIME I get the following error:

select column_time::time

date/time field value out of range: "29:51"

It seems to be expecting the format of HH:MI, so the 29 is out of the range and causing the issue. How can I work around this?

Edit: found this works, but it has leading 00's for hours which I thought weren't there before.

TO_TIMESTAMP(column_time, 'MI:SS')::time

Upvotes: 5

Views: 17508

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658967

There is a subtle difference between a cast and the function call to_timestamp() - even in cases where either would work. The manual:

to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. For most standard date/time formats, simply casting the source string to the required data type works, and is much easier. [...]

One or the other may be what you need.
This works:

SELECT to_timestamp('129:51', 'MI:SS')::time AS col_overtime

02:09:51

But this does not:

SELECT cast('00:' || '129:51' AS time) AS col_overtime

And when simply casting '29:51'::time the minimal format is taken to be HH24:MI, not MI:SS.

SQL Fiddle. (currently dead)

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Try prepending '00:':

select cast(concat('00:', column_time) as time)

Upvotes: 1

Related Questions