Re: format # of seconds to hh:mm
Thomas <cfa532@hotmail.com> wrote:
>cfa532@hotmail.com (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.
>
>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.
I think the answer I sent carefully gave 70 UNITS MINUTE. When I run
the version with 70 UNITS SECOND, I get error -1266 (Intervals or
datetimes are incompatible for the operation), which is a semantic
error and not a syntax error.
I can get decent (non-null) answers from:
CREATE TABLE Dual(Number INTEGER NOT NULL CHECK (Number = 0)
PRIMARY KEY);
INSERT INTO Dual VALUES(0);
SELECT INTERVAL(0:0) HOUR TO MINUTE + (SELECT SUM(tabid) FROM Systables)
UNITS MINUTE FROM Dual;
SELECT INTERVAL(0:0) HOUR TO MINUTE + SUM(tabid) UNITS MINUTE FROM
Systables;
(Here, I'm simply using SysTables.TabID as a convenient source of
integers to sum up -- I happen to get the answer 50:50 from both - so
SUM(tabid) is 3050).
You don't give any indication of how you want seconds to be rounded or
truncated, but these also work:
SELECT INTERVAL(0:0) HOUR TO MINUTE +
TRUNC(SUM(tabid)/60) UNITS MINUTE
FROM SysTables;
SELECT INTERVAL(0:0) HOUR TO MINUTE +
ROUND(SUM(tabid)/60) UNITS MINUTE
FROM SysTables;
These produce the answers 0:50 and 0:51 (conveniently different) on my
machine - YMMV. My testing was on Solaris 8 with IDS 9.30.UC3 (and
SQLCMD, of course).
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
|---------+---------------------------->
| | Jonathan Leffler |
| | <jleffler@earthli|
| | nk.net> |
| | |
| | 06/25/2003 09:20 |
| | PM |
| | |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: Jonathan Leffler/Menlo Park/IBM@IBMUS
|
| cc:
|
| Subject: [Fwd: Re: format # of seconds to hh:mm]
|
|
|
>---------------------------------------------------------------------------------------------------------------------------------------------|
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
----- Message from cfa532@hotmail.com (Thomas) on 25 Jun 2003 16:06:57
-0700 -----
Subject: Re: format # of seconds
to hh:mm
cfa532@hotmail.com (Thomas) wrote in message news:<530f730d.0306241459.72
f5165@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.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/