Correction: Since year(), month() etc is not standard SQL, but scalar functions defined in JDBC/ODBC escape syntax, the portable syntax for this would be:
instead of
> ij> select h,count(*) from (select hour(d) from x) as t(h) group by h;
write:
select h,count(*) from (select {fn hour(d)} from x) as t(h) group by h;
and instead of
> ij> select year(d),month(d),day(d),hour(d),minute(d),second(d) from x;
write:
select {fn year(d)},{fn month(d)},{fn day(d)},{fn hour(d)},{fn minute(d)},{fn
second(d)} from x;
--------------------
Furthermore: year(), month etc in Derby returns 0 if the datetime
values is NULL, so if the column contains NULL values the first query
is wrong and should be
select h,count(*) from (select {fn hour(d)} from x where d is not null) as t(h)
group by h;
However: if hour(d) is to behave like standard SQL extract(hour from
d), then hour(d) should be NULL when d is null. I think there is a bug
in Derby here. Anyway, if hour(d) *had* returned NULL, the proper
query would have been
select h,count(h) from (select {fn hour(d)} from x) as t(h) group by h;
since count(h) should ignore NULL values while count(*) should not.
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
pgpGFMUcBq6Qg.pgp
Description: PGP signature
