CODEWITHSUNDEEP
sqldatetime-formatinformix
mattiedf
mattiedf

Reputation: 23

Transform number of seconds in (Date)Time format 'mm:ss' in Informix SQL

I'm requesting an Informix database with SQL. I have a column with numbers that represent a number of seconds. I want to transform this number to a time (mm:ss) format in my SQL statement. For example, the number '90' should be transformed into '01:30'. It's important that the new field shouldn't be a string field, but a (date)time field.

Upvotes: 0

Views: 1315

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753655

SELECT DATETIME(0:0) MINUTE TO SECOND + colname UNITS SECOND
  FROM data_table

This would convert the row containing a numeric value 90 to the value 01:30 with type DATETIME MINUTE TO SECOND. You can vary the type to deal with larger values:

SELECT DATETIME(0:0:0) HOUR TO SECOND + colname UNITS SECOND
  FROM data_table

This will process non-negative values from 0 to 86399 producing answers from 00:00:99 to 23:59:59 of type DATETIME HOUR TO SECOND.

You can add up to 5 fractional digits of seconds if desired.

If the input values can be negative or 86400 or larger, then you have to define what you want — you will get an error if the value is 3600 in the first example, or 86400 or larger in the second.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can construct this as:

select floor(secs / 60) || ':' || lpad(mod(secs, 60), 2, '0')

Upvotes: 1

Related Questions