format # of seconds to hh:mm
Thomas wanted a number of minutes or seconds shown as HH:MM and got syntax errors with UNITS, INTERVAL and EXTEND. Jonathan Leffler suggested INTERVAL(0:0) HOUR TO MINUTE + 70 UNITS MINUTE. A poster showed the DATETIME variant failing with error 201. Thomas, using SUM with UNITS SECOND, found it returned null and a literal gave a syntax error. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I am trying to trick some Infromix SQL command, so it will format the output of a query into the HH:MM. Say, a row data value 70 will be print as 01:10. I strugglled with UNITS, INTERVAL, EXTEND for a hour, but still get only syntax error. Someone help, please.
Thomas wrote: > I am trying to trick some Infromix SQL command, so it will format the > output of a query into the HH:MM. Say, a row data value 70 will be > print as 01:10. I strugglled with UNITS, INTERVAL, EXTEND for a hour, > but still get only syntax error. Someone help, please. SELECT INTERVAL(0:0) HOUR TO MINUTE + 70 UNITS MINUTES FROM ThereOrThereAbouts It isn't quite clear whether you're dealing with minutes or seconds, but the basic principle applies. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler wrote:
> Thomas wrote:
>
>> I am trying to trick some Infromix SQL command, so it will format the
>> output of a query into the HH:MM. Say, a row data value 70 will be
>> print as 01:10. I strugglled with UNITS, INTERVAL, EXTEND for a hour,
>> but still get only syntax error. Someone help, please.
>
>
> SELECT INTERVAL(0:0) HOUR TO MINUTE + 70 UNITS MINUTES
> FROM ThereOrThereAbouts
>
> It isn't quite clear whether you're dealing with minutes or seconds, but
> the basic principle applies.
>
Well, I run ...
drop table tab1;
create table tab1 (col1 int);
insert into tab1 values (70);select "I like my potatoes", ((datetime (00:00) hour to minute) + col1
units minute) from tab1;
and this produces ...
(constant) (expression)
I like my potatoes 01:10
201: A syntax error has occurred.
Or the following ...
select "I liked my potatoes", ((interval (00:00) hour to minute) + col1
units minute) from tab1;
Mashed
cfa532@hotmail.com (Thomas) wrote in message news:<530f730d.0306241459.72f5165@posting.google.com>... > I am trying to trick some Infromix SQL command, so it will format the > output of a query into the HH:MM. Say, a row data value 70 will be > print as 01:10. I strugglled with UNITS, INTERVAL, EXTEND for a hour, > but still get only syntax error. Someone help, please. Thanks for all the follow-ups. Sorry I did not make it clear. In my code the # of units is second and the column is a SUM. It looks like INTERVAL(00:00) HOUR TO MINUTE + SUM(exp) UNITS SECOND Apparently it does work, the resultset was null, even though the syntax looks good. If I try INTERVAL(00:00) HOUR TO MINUTE + 70 UNITS SECOND, there is a syntax error.