Re: Datetime in group and order by clauses.
>Date: Fri, 15 Jul 1994 23:52:32 +0100 (BST)
>From: Nick Valentine <nick@anix.demon.co.uk>
>Subject: Datetime in group and order by clauses.
>X-Informix-List-Id: <list.4309>
>
>Hi
>
>I have a table with a datetime column (Year to Minute) and I want to
>create sql scripts that will give me statistics group on monthly, daily
>and hourly intervals. Is there any way that I can group and order by
>the components within a datetime file. The manual doesn't mention anything
>about grouping and ordering on datetime columns, at least not in the sections
>I'm looking at.
Assume we have a table:
CREATE TABLE SomeTable
(
Col01 INTEGER NOT NULL,
Col02 DATETIME YEAR TO SECOND NOT NULL
);
Load it with the data:
INSERT INTO SomeTable VALUES (1, '1994-04-26 13:02:03');
INSERT INTO SomeTable VALUES (2, '1994-04-25 13:20:03');
INSERT INTO SomeTable VALUES (3, '1994-04-24 13:00:03');
INSERT INTO SomeTable VALUES (4, '1994-04-23 13:02:43');
INSERT INTO SomeTable VALUES (5, '1994-04-23 13:02:03');
INSERT INTO SomeTable VALUES (6, '1994-04-23 13:20:43');
INSERT INTO SomeTable VALUES (7, '1994-04-23 14:40:03');
INSERT INTO SomeTable VALUES (8, '1994-04-23 14:20:43');
INSERT INTO SomeTable VALUES (9, '1994-04-23 14:24:03');
We can write:
SELECT SUM(Col01), EXTEND(Col02, YEAR TO MONTH) Monthly
FROM SomeTable
GROUP BY 2 {Monthly};
SELECT SUM(Col01), EXTEND(Col02, YEAR TO DAY) Daily
FROM SomeTable
GROUP BY 2 {Daily};
SELECT SUM(Col01), EXTEND(Col02, YEAR TO HOUR) Hourly
FROM SomeTable
GROUP BY 2 {Hourly};
Producing the output:
(sum) monthly
45 1994-04
(sum) daily
1 1994-04-26
2 1994-04-25
3 1994-04-24
39 1994-04-23
(sum) hourly
1 1994-04-26 13
2 1994-04-25 13
3 1994-04-24 13
15 1994-04-23 13
24 1994-04-23 14
I think that deals with the problem -- it's a pity you can't use the
display label (Monthly etc) in the GROUP BY clause, but you get the results
you want.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>