Another Datetime/Interval question
A user on IDS 7.31 (Linux) asked two DATETIME/INTERVAL questions: how to count distinct dates from a DATETIME YEAR TO MINUTE column, and how to get the difference between two such columns expressed as a number of minutes. COUNT(DISTINCT DATE(col)) and COUNT(DISTINCT EXTEND(col, YEAR TO DAY)) both give syntax error 201, so the workaround offered was GROUP BY date(col) or selecting date(col) into a temp table and counting distinct there. For the minutes, since 7.31 has no casts, Jonathan Leffler's trick works: SELECT INTERVAL(0) MINUTE(9) TO MINUTE + (tend - tanf). The poster confirmed both. A follow-up question about comparing a SMALLINT minutes column to an interval (INTERVAL() won't accept a column reference) is left unanswered in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
select date(yourdatetimecol), count(*) from yourtable group by 1
for the second question i would probably create an spl which does the
calc
ugly way would be putting it into a string like
create procedure ugly(a char10)
define...
--"0 02:30"
min = inpvalue[6,7];
hr = inpvalue[3,4];
days = inpvalue[1];
minutestot = (days * 24 * 60) + ( hr * 60 ) + min;
return minutestot;
end procedure;
WARNING not tested...
(or gottrough the manual to see if there is a more descent way...
Superboer.
Richard Spitz schreef:
> 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
Richard Spitz wrote:
> 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.
Use a temp table...
select date(tanf) tanf_date
from foo
into temp foo_tmp with no log;
select count(distinct tanf_date)
from foo_tmp;
>
> 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.
Another temp table...
create temp table bar
(
mins interval minute(5) to minute
) with no log;
insert into bar
select (tend - tanf)
from foo;
select *
from bar;
--
rh
Richard Spitz wrote:
> 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.
You've gotten a number of answers to this. None used EXTEND(tanf, YEAR
TO DAY) that I recall. I'm not sure if you'd be able to use that inside
SELECT COUNT(DISTINCT EXTEND(...))
> 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.
Since you're in 7.31, you don't have casts. So, you have to use a trick:
SELECT INTERVAL(0) MINUTE(9) TO MINUTE + (tend - tanf) FROM whicht;
The first term simply dictates the units used by the rest of the
expression. You might be OK with 0 UNITS MINUTE, or you might run into
problems for procedures lasting more than 99 minutes.
(I see Andrew gave this solution too, based - he said - on a Tech Note
from September 2005. I've not seen the note; I've given the answer
before on c.d.i, though, in slightl different guises.)
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Jonathan Leffler <jleffler@earthlink.net> schrieb:
>You've gotten a number of answers to this. None used EXTEND(tanf, YEAR
>TO DAY) that I recall. I'm not sure if you'd be able to use that inside
>SELECT COUNT(DISTINCT EXTEND(...))
select count(distinct extend(tanf, year to day)) from <table>
201: A syntax error has occured
Looks like the approach with the intermediate temp table is the only
solution. It's not pretty, but it will deliver the desired result.
>Since you're in 7.31, you don't have casts. So, you have to use a trick:
>
>SELECT INTERVAL(0) MINUTE(9) TO MINUTE + (tend - tanf) FROM whicht;
>
>The first term simply dictates the units used by the rest of the
>expression. You might be OK with 0 UNITS MINUTE, or you might run into
>problems for procedures lasting more than 99 minutes.
That's the kind of trick I like. Very elegant workaround for the lack of
casts in 7.31!
>(I see Andrew gave this solution too, based - he said - on a Tech Note
>from September 2005. I've not seen the note; I've given the answer
>before on c.d.i, though, in slightl different guises.)
Yes, I found your answer in Google after quite some time, using the
search term "to minute".
Regards, Richard
And on to the next INTERVAL problem. We are still in IDS 7.31, so no casts. I have a table where the duration of a procedure (in minutes) is stored in a smallint column. Another table stores beginning and end of a procedure in "datetime year to minute" columns. I found no way to compare durations between these tables. Subtracting end from beginning in the second table yields an interval, which I can "convert" into the duration in minutes with Jonathan's trick. But there seems to be no way to convert or compare smallint to interval. I tried "SELECT INTERVAL(<column>) MINUTE(4) TO MINUTE FROM <table>" but that gives me a syntax error. Why can I use a constant integer as an argument with the INTERVAL qualifier, but not a column reference? Regards, Richard