Re: Retrieve 3o minutes [395]
On Sat, Dec 27, 2008 at 6:14 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Sorry, I always get the INTERVAL constants wrong, it should be:
>
> select ...
> from mytable
> where .....
> and insert_time BETWEEN (CURRENT - INTERVAL (30) minute(2) to minute)
> and CURRENT> ....
> ;
>
> -OR I could have used the UNITS as you did:
>
> select ...
> from mytable
> where .....
> and insert_time BETWEEN (CURRENT - (30) UNITS minute) and CURRENT> ....
> ;
>
> But both solutions require that the time column, here insert_time, is of
> resolution DATETIME YEAR to MINUTE or finer.
>
> Some part of your problem is because you have the date and time in separate
> columns. That gives you problems when the subtraction of the time moves you
> to an earlier day. In addition, you want to report the total number of rows
> (count(*)) within each 30 minute period, that's a whole different ball of
> wax and you can't do that simply in SQL. To fix that is difficult, but
> let's try. I'm going to use a stored procedure to do all of the work
> because working with datetimes and char values inline is awkward at best.
> Another solution would be to call a stored procedure/function from the
> projection clause to return the time interval values for a given time (say
> return 00:00 from 00:00 to 00:29 then 00:30 from 00:30 to 00:59, etc)
> including just the day, function return, and a count(*) in the projection
> clause and use a GROUP BY clause on the day and interval.
>
> create function summarize_by_time( start_day date, start_time datetime hour
> to hour )
> returning datetime year to day as row_date, datetime hour to minute as
> starttime, int as calls;>
> define day datetime year to day, time datetime hour to minute;
> define intvl, now, compare datetime year to second;
> int counter;
>
> LET counter = 0;
>
> LET now = (start_day || ' ' || start_time || ':30') datetime year to minute;
> LET intvl = (start_day || ' ' || start_time) datetime year to minute;
>
> FOREACH
> SELECT row_date, starttime
> FROM hvdn
> WHERE row_date > start_day AND starttime > (start_time::DATETIME HOUR> TO MINUTE);
>
> LET compare = (row_date || ' ' || starttime);
> IF now <= compare THEN
> RETURN intvl::DATETIME YEAR TO DAY, intvl::DATETIME HOUR TO
> MINUTE, counter WITH RESUME;
> LET counter = 0;
> LET compare = compare + (30) UNITS MINUTES;
> LET intvl = intvl + (30) UNITS MINUTES;
> ELSE
> LET counter = counter + 1;
> END IF;
> END FOREACH;
> RETURN intvl::DATETIME YEAR TO DAY, intvl::DATETIME HOUR TO MINUTE, counter;
>
> end function;
>
> EXECUTE FUNCTION summarize_by_time( current year to day, (current hour to> minute - (55) units minute) );
>
> Note that the return value naming will only work in IDS 11.10 and later.
>
> Art
>
>
> On Sat, Dec 27, 2008 at 4:43 PM, Ravi Rasu <ravi.rasu@gmail.com> wrote:
>>
>> select * from HVDN where hvdn.row_date = today and hvdn.starttime >
>> REPLACE((current hour to minute - 55 units minute) - today,":","")>>
>> Thank you for your email.
>> when i use (CURRENT - (30) INTERVAL minute(2) to minute) i am getting
>> syntac error.
>>
>> for ex table values are like this
>>
>> row_date starttime calls
>> 12/27/2008 0 87
>> 12/27/2008 30 8
>> 12/27/2008 100 889
>> ....
>> ...
>> 12/27/2008 2330 99
>>
>> the final 2330 values comes at 12/28/2008 after 12 AM ..
>>
>> I have to query these records
>> for ex if i subtract 45 minutes or so .how do i round the value to 30
>> minutes interval..
>>
>> for ex 1142 to 1130 ..
>> 1210 to 1200
>>
>> Please let me know your thoughts
>>
>> Thank you for your help
>>
>>
>>
>>
>> On Fri, Dec 26, 2008 at 3:22 PM, Art Kagel <art.kagel@gmail.com> wrote:
>>>
>>> Assuming the table has a DATETIME YEAR TO MINUTE or higher resolution
>>> column
>>> in it (assume that column is called insert_time):
>>>
>>> select ...
>>> from mytable
>>> where .....>>>
>>> and insert_time between (CURRENT - (30) INTERVAL minute(2) to minute)
>>> and CURRENT
>>> ....
>>> ;
>>>
>>> Art
>>>
>>> On Fri, Dec 26, 2008 at 12:55 PM, ROB RAS <ravi.rasu@gmail.com> wrote:
>>>
>>> > How to retreive last 30 minutes data from informix?
>>> > for ex interval format will be 1100 ,1130,1200,1230 ,1300 and 1330 for
>>> > every
>>> > half hour.
Here's a somewhat different approach. It can be done (but anything
but easily) in pure SQL; there is also a stored procedure which is
easier to understand. It might be possible to do without the explicit
variable 's' - I had a syntax error earlier which I eventually fixed
but I didn't go back and undo the variable 's'.
The approach I'd advocate is to convert the time into an integer that
can be used for grouping, with different integers representing
different half-hour intervals within a day.
create temp table t (t datetime year to minute not null);
insert into t(t) values('2009-01-08 12:11');
insert into t(t) values('2009-01-08 12:01');
insert into t(t) values('2009-01-08 12:04');
insert into t(t) values('2009-01-08 12:05');
insert into t(t) values('2009-01-08 12:08');
insert into t(t) values('2009-01-08 12:03');
insert into t(t) values('2009-01-08 12:06');
insert into t(t) values('2009-01-08 12:09');
insert into t(t) values('2009-01-08 12:15');
insert into t(t) values('2009-01-08 12:15');
insert into t(t) values('2009-01-08 12:18');
insert into t(t) values('2009-01-08 12:21');
insert into t(t) values('2009-01-08 12:24');
insert into t(t) values('2009-01-08 12:27');
insert into t(t) values('2009-01-08 12:30');
insert into t(t) values('2009-01-08 12:33');
insert into t(t) values('2009-01-08 12:36');
insert into t(t) values('2009-01-08 12:39');
insert into t(t) values('2009-01-08 12:42');
insert into t(t) values('2009-01-08 12:45');
insert into t(t) values('2009-01-08 12:48');
insert into t(t) values('2009-01-08 12:51');
insert into t(t) values('2009-01-08 12:54');
insert into t(t) values('2009-01-08 12:57');
SELECT t,
EXTEND(t, YEAR TO DAY) AS DATE,
(t - EXTEND(t, YEAR TO DAY))::INTERVAL MINUTE(4) TO MINUTE AS
minutes_since_midnight,
((t - EXTEND(t, YEAR TO DAY))::INTERVAL MINUTE(4) TO
MINUTE)::CHAR(5) AS str_mins_0000,
(((t - EXTEND(t, YEAR TO DAY))::INTERVAL MINUTE(4) TO
MINUTE)::CHAR(5))::INTEGER AS int_mins_0000,
TRUNC(((((t - EXTEND(t, YEAR TO DAY))::INTERVAL MINUTE(4) TO
MINUTE)::CHAR(5))::INTEGER)/30, 0) AS int_30_min_group,
((30 * TRUNC(((((t - EXTEND(t, YEAR TO DAY))::INTERVALMINUTE(4) TO MINUTE)::CHAR(5))::INTEGER)/30, 0)) UNITS MINUTE) AS
start_interval_ivmm,
((30 + 30 * TRUNC(((((t - EXTEND(t, YEAR TO DAY))::INTERVAL
MINUTE(4) TO MINUTE)::CHAR(5))::INTEGER)/30, 0)) UNITS MINUTE) AS
end_interval_ivmm,
((30 * TRUNC(((((t - EXTEND(t,