Re: Another Datetime/Interval question
Umm, I don't know if IDS 7.31UC8 support casting...
But IDS 9.x and 10 does.
If IDS 7.31 supports casting then you can do something like this:
select "2005-12-21 15:27:33"::datetime year to second
as tend,
"2005-12-21 17:57:33"::datetime year to second
as tanf,
"2005-12-21 17:57:33"::datetime year to second
-
"2005-12-21 15:27:33"::datetime year to second
as elap,
("2005-12-21 17:57:33"::datetime year to second
-
"2005-12-21 15:27:33"::datetime year to second)
::interval minute(3) to minute as elap_min,
("2005-12-21 17:57:33"::datetime year to second
-
"2005-12-21 15:27:33"::datetime year to second)
::interval minute(3) to minute::char(4)::int+10
as elap_int_plus_10
from dual
;
Which gives these results:
tend 2005-12-21 15:27:33
tanf 2005-12-21 17:57:33
elap 0 02:30:00
elap_min 150
elap_int_plus_10 160
Note that the last expresion is casted to an int through a char because there is no direct cast from interval to int.
Hope this help.
J.
-----Original Message-----
From: Richard Spitz <Richard.Spitz@med.uni-muenchen.de>
To: informix-list@iiug.org
Date: Fri, 16 Dec 2005 16:36:42 +0100
Subject: Another Datetime/Interval question
Dear Informixers,
I'm stuck with a datetime/interval problem again. Database is IDS 7.31UC8 on
Linux. I'm presently working with plain old dbaccess, although ODBC might
be needed in the future.
I have a table whicht contains(among others) two DATETIME columns:
tanf datetime year to minute,
tend datetime year to minute
First question:
How do I select the number of different dates for which records exist?
I tried
SELECT COUNT(DISTINCT DATE(tanf)) FROM <tablename>
but that gives me a syntax error.
Second question:
How do I select the number of minutes between "tanf" and "tend"? "tanf"
stores the beginning of a (medical) procedure, "tend" stores the end.
SELECT tend - tanf gives me the difference (interval) in days, hours and
minutes (e.g. "0 02:30"), but in this case, I need "150" as the number
of minutes passed.
I experimented a lot with the EXTEND function and the INTERVAL qualifier,
but nothing worked.
Any ideas?
Regards, Richard
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
sending to informix-list