Home » SQL & PL/SQL » SQL & PL/SQL » Avarege over 5 minutes
| Avarege over 5 minutes [message #287038] |
Tue, 11 December 2007 00:05  |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Hi all
I have been working on a script ( see script below ) to get an average time between to event's out of a database. I did mange to get that script working for averages over an hour. ( With some help ) But now I need the averages over 5 minutes.
What have I tried. I have tried to add minutes to my script. Then use the between statement in the over. But that gives me a rolling average. I need a true average in a time frame of 5 minutes. This means i wil get just 12 data ouputs for every hour not 60.
I looked every where yesterday to come up with an example of a real average but could not find one. So I am turning to this forum to help me.
SELECT DISTINCT AVG.day
, AVG.month
, AVG.year
, AVG.h
, AVG.AVG
FROM
(SELECT AVG(nvl(AVR_T.T_D_sec,0) )
OVER ( PARTITION by mod_day.year , mod_day.month , mod_day.day , mod_day.h ) AVG
, mod_day.DAY
, mod_day.month
, mod_day.year
, mod_day.h h
, AVr_T.r_date
, AVr_T.r_hour
, AVr_T.r_day
, AVr_T.r_month
, AVr_T.r_year
FROM
(select
SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour
,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+
(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+
SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
FROM
TMS_MESSAGE_AUDIT_LOG WCA ,
TMS_MESSAGE_AUDIT_LOG REC
WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND
REC.MESSAGE_STATUS = '(Receive)' AND
WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and
WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1)
) AVR_T ,
(SELECT
SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),0,2) DAY
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),4,3) month
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'dd-MON-yy'),8,2) year
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/25), 'hh24') h
FROM
dual
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 25
) Mod_day
WHERE mod_day.h = R_hour (+) and
mod_day.day = AVR_T.R_day (+) and
mod_day.month = AVR_T.R_month (+)
) AVG
;
Result of this code is
DAY MONTH YEAR H AVG
05 DEC 07 00 0
05 DEC 07 01 0
05 DEC 07 02 0
05 DEC 07 03 0
05 DEC 07 04 0
05 DEC 07 05 0
05 DEC 07 06 0
05 DEC 07 07 0
05 DEC 07 08 0
05 DEC 07 09 0
05 DEC 07 10 4.33333333333333
05 DEC 07 11 0
05 DEC 07 12 5
05 DEC 07 13 0
05 DEC 07 14 5.75
05 DEC 07 15 6
05 DEC 07 16 0
05 DEC 07 17 0
05 DEC 07 18 0
05 DEC 07 19 0
05 DEC 07 20 0
05 DEC 07 21 0
05 DEC 07 22 0
05 DEC 07 23 0
The table creation and data is in the attached file.
Marcel Koert
[Updated on: Tue, 11 December 2007 00:47] by Moderator Report message to a moderator |
|
|
|
| Re: Avarege over 5 minutes [message #287066 is a reply to message #287038] |
Tue, 11 December 2007 00:52  |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Hi
I just got one step further.
I wrote the following script. This is my Model day that is part of the average's script. I found how i can get 5 minutes intervals. Still not perfect because it gives only 24 lines but it is a step.
SELECT
SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),0,2) DAY
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),4,3) month
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),8,2) year
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
dual
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288
But it gives only a result of 24 lines i will need 288.
DAY MONTH YEAR H M
05 DEC 07 00 05
05 DEC 07 00 10
05 DEC 07 00 15
05 DEC 07 00 20
05 DEC 07 00 25
05 DEC 07 00 30
05 DEC 07 00 35
05 DEC 07 00 40
05 DEC 07 00 45
05 DEC 07 00 50
05 DEC 07 00 55
05 DEC 07 01 00
05 DEC 07 01 05
05 DEC 07 01 10
05 DEC 07 01 15
05 DEC 07 01 20
05 DEC 07 01 25
05 DEC 07 01 30
05 DEC 07 01 35
05 DEC 07 01 40
05 DEC 07 01 45
05 DEC 07 01 50
05 DEC 07 01 55
06 DEC 07 02 00
06 DEC 07 02 05
Marcel Koert
|
|
|
|
| Re: Avarege over 5 minutes [message #287093 is a reply to message #287066] |
Tue, 11 December 2007 01:30 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
I get 287 rows, as I suspected. One tip though: you can rewrite the connect-by clause as :
Date calculations are done in units of days. you subtract two subsequent days without time information, resulting in the constant "1".
MHE
[Updated on: Tue, 11 December 2007 01:30] Report message to a moderator |
|
|
|
| Re: Avarege over 5 minutes [message #287100 is a reply to message #287093] |
Tue, 11 December 2007 01:44 |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Hi
The reason that i put the two date's in there is that i want to be able to run this query over a longer period then 1 day. Something like
SELECT
SUBSTR(to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),0,2) DAY
,SUBSTR(to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),4,3) month
,SUBSTR(to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/24), 'dd-MON-yy'),8,2) year
,to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/288), 'hh24') h
,to_char(trunc (to_date('01-NOV-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
dual
connect by level < (to_date('30-NOV-07','dd-mon-rr') - to_date('01-NOV-07','dd-mon-rr')) * 288
I have tried this now in TOAD and in SQLPLUS in both i am getting different results. TOAD only gives me 24 lines SQLPLUS gives me 1 line of output. What am i missing here if you can get 288 lines of output.
Marcel Koert
|
|
|
|
|
|
| Re: Avarege over 5 minutes [message #287109 is a reply to message #287038] |
Tue, 11 December 2007 01:49 |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Hi
SQL*Plus: Release 9.2.0.7.0 - Production on Tue Dec 11 08:37:10 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
JServer Release 9.2.0.8.0 - Production
TOAD
8.0.0.47
Marcel Koert
|
|
|
|
|
|
|
|
| Re: Avarege over 5 minutes [message #287120 is a reply to message #287038] |
Tue, 11 December 2007 02:27 |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Thanks for that
That solved the model day. The model day now looks like
select *
from
(SELECT
SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),0,2) DAY
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),4,3) month
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),8,2) year
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
dual
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288)
Now the problem of the average's. If i use this model day that we now created the averages will be of. How do i now get the avg statment to calculated the avrages between two points in my modelday.
What i have tried is this
SELECT DISTINCT AVG.day
, AVG.month
, AVG.year
, AVG.h
, AVG.M
, AVG.AVG
FROM
(SELECT AVG(nvl(AVR_T.T_D_sec,0) ) OVER ( PARTITION by mod_day.year , mod_day.month , mod_day.day , mod_day.h , mod_day.M) AVG
, mod_day.DAY
, mod_day.month
, mod_day.year
, mod_day.h h
, mod_day.M M
, AVr_T.r_date
, AVr_T.r_hour
, AVr_T.r_day
, AVr_T.r_month
, AVr_T.r_year
FROM
(select
SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour
,SUBSTR(REC.LOCAL_TM,12,2 ) R_min
,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+
(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+
SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
FROM
TMS_MESSAGE_AUDIT_LOG WCA ,
TMS_MESSAGE_AUDIT_LOG REC
WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND
REC.MESSAGE_STATUS = '(Receive)' AND
WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and
WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1)
) AVR_T ,
(SELECT
SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),0,2) DAY
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),4,3) month
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),8,2) year
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
dual
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288
) Mod_day
WHERE mod_day.h = R_hour (+) and
mod_day.day = AVR_T.R_day (+) and
mod_day.month = AVR_T.R_month (+)
) AVG
;
But then it still calculates the averages over the hour and just display's them 12 times. I know this would not work because it is just a normal over statment i need something that does a averages between a point in the model day and the next point in the model day. And then takes the next point.
Marcel Koert
|
|
|
|
| Re: Avarege over 5 minutes [message #287130 is a reply to message #287038] |
Tue, 11 December 2007 02:50 |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Hi all
I just need a confirmation.
I think i just solved it but i want to be sure.
This is the code
SELECT DISTINCT AVG.day
, AVG.month
, AVG.year
, AVG.h
, AVG.M
, AVG.AVG
FROM
(SELECT AVG(nvl(AVR_T.T_D_sec,0) )
OVER ( PARTITION by mod_day.year , mod_day.month , mod_day.day , mod_day.h , mod_day.M ) AVG
, mod_day.DAY
, mod_day.month
, mod_day.year
, mod_day.h h
, mod_day.M M
, AVr_T.r_date
, AVr_T.R_min
, AVr_T.r_hour
, AVr_T.r_day
, AVr_T.r_month
, AVr_T.r_year
FROM
(select
SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),0,10) r_date
,SUBSTR(REC.LOCAL_TM,10,2 ) R_hour
,SUBSTR(REC.LOCAL_TM,12,2 ) R_min
,SUBSTR(REC.LOCAL_TM,3,2 ) R_year
,SUBSTR(TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'),4,3) r_month
,SUBSTR(REC.LOCAL_TM,7,2 ) R_day
,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+
(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+
SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
FROM
TMS_MESSAGE_AUDIT_LOG WCA ,
TMS_MESSAGE_AUDIT_LOG REC
WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND
REC.MESSAGE_STATUS = '(Receive)' AND
WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and
WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1)
) AVR_T ,
(SELECT
SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),0,2) DAY
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),4,3) month
,SUBSTR(to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'dd-MON-yy'),8,2) year
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'hh24') h
,to_char(trunc (to_date('05-DEC-07','dd-MON-rr')) + (level/288), 'MI') M
FROM
dual
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('05-DEC-07','dd-mon-rr')) * 288
) Mod_day
WHERE mod_day.h = R_hour (+) and
mod_day.day = AVR_T.R_day (+) and
mod_day.month = AVR_T.R_month (+) and
mod_day.m = AVR_T.R_min(+)
) AVG
;
Does this real calculate the averages between every 5 minuuts.
If so it was simpler then i thought.
Marcel Koert
|
|
|
|
| Re: Avarege over 5 minutes [message #287136 is a reply to message #287130] |
Tue, 11 December 2007 03:17 |
 |
Michel Cadot
Messages: 68771
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This may be of some help:
SQL> create table t (dt date, val integer);
Table created.
SQL> insert into t
2 select sysdate-dbms_random.value(0,2000)/86400,
3 round(dbms_random.value(1,100),2)
4 from dual
5 connect by level <= 30
6 /
30 rows created.
SQL> select * from t order by dt;
DT VAL
------------------- ----------
11/12/2007 09:42:19 46
11/12/2007 09:42:45 61
11/12/2007 09:43:03 6
11/12/2007 09:43:08 99
11/12/2007 09:43:42 61
11/12/2007 09:44:18 11
11/12/2007 09:45:16 13
11/12/2007 09:46:49 79
11/12/2007 09:49:30 72
11/12/2007 09:50:04 50
11/12/2007 09:50:57 88
11/12/2007 09:53:05 3
11/12/2007 09:53:56 95
11/12/2007 09:55:23 98
11/12/2007 09:56:46 2
11/12/2007 09:58:08 94
11/12/2007 10:01:26 44
11/12/2007 10:02:49 90
11/12/2007 10:04:41 72
11/12/2007 10:08:03 10
11/12/2007 10:10:24 78
11/12/2007 10:11:14 65
11/12/2007 10:12:08 39
11/12/2007 10:12:43 28
11/12/2007 10:13:08 2
11/12/2007 10:13:22 20
11/12/2007 10:13:41 68
11/12/2007 10:13:56 96
11/12/2007 10:14:40 63
11/12/2007 10:15:20 55
30 rows selected.
SQL> with
2 times as (select trunc(sysdate-1/24,'HH')+5*level/1440 tim from dual connect by level <= 18)
3 select to_char(tim,'DD/MM/YYYY HH24:MI') "Time", nvl(round(avg(val),2),0) "Avg"
4 from times, t
5 where t.dt (+) between times.tim and times.tim+5/1440-1/86400
6 group by times.tim
7 order by times.tim
8 /
Time Avg
---------------- ----------
11/12/2007 09:05 0
11/12/2007 09:10 0
11/12/2007 09:15 0
11/12/2007 09:20 0
11/12/2007 09:25 0
11/12/2007 09:30 0
11/12/2007 09:35 0
11/12/2007 09:40 47.33
11/12/2007 09:45 54.67
11/12/2007 09:50 59
11/12/2007 09:55 64.67
11/12/2007 10:00 68.67
11/12/2007 10:05 10
11/12/2007 10:10 51
11/12/2007 10:15 55
11/12/2007 10:20 0
11/12/2007 10:25 0
11/12/2007 10:30 0
18 rows selected.
Regards
Michel
|
|
|
|
| Re: Avarege over 5 minutes [message #287171 is a reply to message #287038] |
Tue, 11 December 2007 05:26 |
mklynx
Messages: 17
Registered: December 2007
|
Junior Member |
|
|
Thanks everybody.
Micheal from you post i have created the latest script that does what it should do.
select to_char(times.tim,'DD/MM/YYYY HH24:MI') "Time"
,nvl(round(avg(T_D_sec),2),0) "Avg"
from
(select
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3') r_date
,(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+1,2)*3600)+
(SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+4,2)*60)+
SUBSTR((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')),
INSTR(((TO_TIMESTAMP (WCA.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3')-
TO_TIMESTAMP (REC.LOCAL_TM, 'YYYYMMDD HH24:MI:SS.FF3'))),' ')+7,2) T_D_sec
FROM
TMS_MESSAGE_AUDIT_LOG WCA ,
TMS_MESSAGE_AUDIT_LOG REC
WHERE WCA.MESSAGE_ID = REC.MESSAGE_ID AND
REC.MESSAGE_STATUS = '(Receive)' AND
WCA.MESSAGE_STATUS = 'Wait CLSB Ack' and
WCA.MESSAGE_version = (REC.MESSAGE_VERSION+1)
) AVR_T ,
(select
trunc((to_timestamp('03/12/2007 01:00:00','dd/mm/yyyy hh24:mi:ss'))-1/24,'HH')+5*level/1440 tim
from
dual
connect by level < (to_date('6-DEC-07','dd-mon-rr') - to_date('03-DEC-07','dd-mon-rr')) * 288 ) times
where AVR_T.R_date (+) between times.tim and times.tim+5/1440-1/86400
group by to_char(times.tim,'DD/MM/YYYY HH24:MI')
order by to_char(times.tim,'DD/MM/YYYY HH24:MI')
;
And this works like a charm.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 18 12:55:54 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
