Datetime question
Hi all,
I'm using online 5.10 and I can't figure out how to write the following
query
select *
from A,B
where
A.d + A.t > B.d+12 units hour
where
A.d and B.d are of type "date" and A.t is a "datetime hour to second"
So the expression in the where clause would be true if
A.d = "6/1/1999" and A.t="13:45:00" and B.d="6/1/1999",
but it would be false if
A.d = "6/1/1999" and A.t="11:00:00" and B.d="6/1/1999"
Sorry if this question is too stupid. I've already tried several
combinations of extend(), datetime() and interval() and keep getting
syntax or type compatibility errors.
Thanks in advance
--
Guillermo Labatte
Guillermo Labatte wrote:
>
> Hi all,
> I'm using online 5.10 and I can't figure out how to write the following
> query
>
> select *
> from A,B
> where
> A.d + A.t > B.d+12 units hour>
> where
> A.d and B.d are of type "date" and A.t is a "datetime hour to second"
>
> So the expression in the where clause would be true if
> A.d = "6/1/1999" and A.t="13:45:00" and B.d="6/1/1999",
> but it would be false if
> A.d = "6/1/1999" and A.t="11:00:00" and B.d="6/1/1999"
Oh boy are you in a fix. OK, it ain't easy but here's what you have
to do:
1) create a temp table to hold intermediate and compatible results:
create temp table tt (
k integer,
d datetime year to second,
t interval hour(2) to second
);
2) Populate tt converting column t to an interval:
insert into tt
select rowid, d, t - extend("00:00:00", hour to second) from A;
3) Join back to perform comparisons:
SELECT A.*, B.*
FROM A, B, tt
WHERE A.rowid = tt.key
AND ((tt.d + tt.t) >
(extend( B.d, year to second ) + interval(12) hour(2) to hour));
You could try it in a single step but I'm not sure it will pass the
parser. It would look like this:
SELECT *
FROM A, B
WHERE (
(extend( A.d, year to second) + {Convert d->datetime}
(t - extend("00:00:00", hour to second))) > {Make t->interval)
(extend( B.d, year to second ) + interval(12) hour(2) to hour)
);
Art S. Kagel
I wonder if the datetime column could be changed to an interval hour to
second, which is what it really is. I was able to get this to work:
create table a
( adate date,
atime interval hour to second );
create table b ( bdate date );
insert into a values (today, "12:30:00");
insert into b values (today);
insert into b select adate - 1 from a;
insert into a values (today, "08:30:00");--
select *
from a, b
where extend( a.adate, year to second) + atime >
extend( b.bdate, year to second) + 12 units hour;
If you can't change the datatype of the column, I was able to get it to work
by sidestepping into a temp table:
create table a
( adate date,
atime datetime hour to second );
create table b ( bdate date );
insert into a values (today, "12:30:00");
insert into b values (today);
insert into b select adate - 1 from a;
insert into a values (today, "08:30:00");
create temp table c (adate date, atime interval hour to second );
insert into c select adate, atime - datetime( 0:00:00 ) hour to second
from a;--
select *
from c, b
where extend( c.adate, year to second) + atime >
extend( b.bdate, year to second) + 12 units hour;
This may not be the most efficient way, but it's what you get for free.
Guillermo Labatte wrote in message <7j1855$n6q$1@news.xmission.com>...
>
>Hi all,
>I'm using online 5.10 and I can't figure out how to write the following
>query
>
>select *
>from A,B
>where
> A.d + A.t > B.d+12 units hour>
>where
>A.d and B.d are of type "date" and A.t is a "datetime hour to second"
>
>So the expression in the where clause would be true if
>A.d = "6/1/1999" and A.t="13:45:00" and B.d="6/1/1999",
>but it would be false if
>A.d = "6/1/1999" and A.t="11:00:00" and B.d="6/1/1999"
>
>Sorry if this question is too stupid. I've already tried several
>combinations of extend(), datetime() and interval() and keep getting
>syntax or type compatibility errors.
>
>Thanks in advance
>
>--
>Guillermo Labatte
>
>