Answer to both of Legolas' questions: Consider table x with a timestamp field like this:
ij> select * from x; D -------------------------- 2005-11-28 10:26:33.0 2005-11-28 10:26:33.0 2005-11-28 11:26:33.0 2005-11-28 13:26:33.0 2005-11-28 14:26:33.0 2005-11-28 14:00:33.0 2005-11-28 14:01:33.0 To count the number of timestamps and group them by the hour: ij> select h,count(*) from (select hour(d) from x) as t(h) group by h; H |2 ----------------------- 10 |2 11 |1 13 |1 14 |3 To extract the year, month, day etc. from table x: ij> select year(d),month(d),day(d),hour(d),minute(d),second(d) from x; 1 |2 |3 |4 |5 |6 ---------------------------------------------------------------------------------- 2005 |11 |28 |10 |26 |33.0 2005 |11 |28 |10 |26 |33.0 2005 |11 |28 |11 |26 |33.0 2005 |11 |28 |13 |26 |33.0 2005 |11 |28 |14 |26 |33.0 2005 |11 |28 |14 |0 |33.0 2005 |11 |28 |14 |1 |33.0 >>>>>>>>>>>> Legolas Woodland wrote (2005-11-27 23:44:24): > Hi > Thank you for reading my post. > is there any way to count total records in each group of records when > using "group by" command ? > for example i need to find how much people log in each hour of a day. > in this case i can use group by to group the record based on hour field > and then use count to find how much people log in in each hour out of 24 > :-) . > please tell me if my design is not correct. > > BTW : > I get one field for each element of Data time , is it a good design ? i > have these fields for year , Month , Day , Hour , minutes , seconds . > is it good or i can use a dateTime field and sql let me to access each > element in datetime field ?) > > >>>>>>>>>>>> Legolas Woodland wrote (2005-11-28 12:42:07): > Hi > Thank you for reply. > i get one field for each of a DATETIME element in my table. > it means that i have : year , month, day , hour, minute, second . > i do this to be able to use each element in my queries. i find that in > Oracle it is possible to extract each of those elements in sql commands > and use them. > now my question is how i can do the same (using a datetime and then > access to each of the element) in derby ? > -- Bernt Marius Johnsen, Database Technology Group, Sun Microsystems, Trondheim, Norway
pgp93h1rfiVL9.pgp
Description: PGP signature
