Encore merci...
Michel Cadot a écrit dans le message
<82g4i5$orou$1_at_oceanite.cybercable.fr>...
>Voici un package de Steve Baldwin <steven.baldwin_at_hancorp.com.au>
>pour convertir un nombre de secondes depuis le 01/01/1970 en
>format date Oracle et vice-versa.
>Il suffit alors de remplacer, dans ma requête précédente,
>"timestamp" par "PKG_UnixDate.From_DateU (timestamp)"
>(si "timestamp" est la colonne contenant le nombre de secondes).
>
>CREATE OR REPLACE PACKAGE PKG_UnixDate AS
> FUNCTION To_DateU (i_date IN DATE) RETURN PLS_INTEGER ;
> PRAGMA RESTRICT_REFERENCES (To_DateU, WNDS, RNDS) ;
> FUNCTION From_DateU (i_Udate IN PLS_INTEGER) RETURN DATE ;
> PRAGMA RESTRICT_REFERENCES (From_DateU, WNDS, RNDS) ;
>END pkg_UnixDate ;
>/
>CREATE OR REPLACE PACKAGE BODY pkg_UnixDate AS
> k_BaseDate CONSTANT DATE := to_date('01011970','DDMMYYYY');
> k_SecsPerDay CONSTANT PLS_INTEGER := 86400;
> k_BaseDate_J CONSTANT PLS_INTEGER := to_char(k_BaseDate, 'J');
>--
>FUNCTION To_DateU (i_date IN DATE) RETURN PLS_INTEGER IS
>BEGIN
> RETURN ((to_char(i_date,'J') - k_BaseDate_J) * k_SecsPerDay) +
>to_char(i_date,'SSSSS');
>END ;
>--
>FUNCTION From_DateU (i_Udate IN PLS_INTEGER) RETURN DATE IS
>BEGIN
> RETURN k_BaseDate + (i_Udate / k_SecsPerDay);
>END ;
>END PKG_UnixDate ;
>/
>
>
>--
>Bonne journée
>Michel
>
>
>Vigi98 <hubera_at_caramail.com> a écrit dans le message :
>xSJ14.55$Xc5.279674_at_nnrp2.proxad.net...
>> En fait mes dates sont en secondes depuis le 1/1/70. Pourriez-vous
adapter
>> la requête en fonction de cette nouvelle donnée. J'ai essayé, mais pour
>> l'instant les résultats ne sont pas très probants.
>>
>> Merci d'avance.
>>
>>
>> Michel Cadot a écrit dans le message
>> <825uil$8phf$1_at_oceanite.cybercable.fr>...
>> >Try this:
>> >create table alarm (alarm_id number, timestamp date, severity number);
>> >
>> >insert into alarm values(1, trunc(sysdate), 2);
>> >insert into alarm values(2, trunc(sysdate)+1/24, 1);
>> >insert into alarm values(3, trunc(sysdate)+2/24, 1);
>> >insert into alarm values(4, trunc(sysdate)+3/24, 2);
>> >insert into alarm values(5, trunc(sysdate)+4/24, 3);
>> >insert into alarm values(6, trunc(sysdate)+5/24, 2);
>> >
>> >insert into alarm values(7, trunc(sysdate)+6/24, 1);
>> >insert into alarm values(8, trunc(sysdate)+7/24, 1);
>> >insert into alarm values(9, trunc(sysdate)+8/24, 2);
>> >insert into alarm values(10, trunc(sysdate)+9/24, 2);
>> >insert into alarm values(11, trunc(sysdate)+10/24, 1);
>> >insert into alarm values(12, trunc(sysdate)+11/24, 3);
>> >insert into alarm values(13, trunc(sysdate)+12/24, 3);
>> >insert into alarm values(14, trunc(sysdate)+13/24, 2);
>> >insert into alarm values(15, trunc(sysdate)+14/24, 2);
>> >insert into alarm values(16, trunc(sysdate)+15/24, 1);
>> >insert into alarm values(17, trunc(sysdate)+16/24, 1);
>> >insert into alarm values(18, trunc(sysdate)+17/24, 2);
>> >
>> >insert into alarm values(19, trunc(sysdate)+18/24, 2);
>> >insert into alarm values(20, trunc(sysdate)+19/24, 3);
>> >insert into alarm values(21, trunc(sysdate)+20/24, 3);
>> >insert into alarm values(22, trunc(sysdate)+21/24, 1);
>> >insert into alarm values(23, trunc(sysdate)+22/24, 1);
>> >insert into alarm values(24, trunc(sysdate)+23/24, 1);
>> >
>> >commit;
>> >
>> >v734> select substr(to_char(trunc(timestamp-21599/86400),
>> 'DD-MON-YYYY'),1,11)
>> >"Date",
>> > 2 decode(trunc((to_number(to_char(timestamp,'SSSSS'))-21599+
>> > 3
>> >decode(sign(to_number(to_char(timestamp,'SSSSS'))-21599),
>> > 4 -1, 86400, 0))/43200),
>> > 5 0, 'Yes', 'No') "Between",
>> > 6 sum(decode(severity,1,1,0)) "Sev 1",
>> > 7 sum(decode(severity,2,1,0)) "Sev 2",
>> > 8 sum(decode(severity,3,1,0)) "Sev 3"
>> > 9 from alarm
>> > 10 group by trunc(timestamp-21599/86400),
>> > 11 trunc((to_number(to_char(timestamp,'SSSSS'))-21599+
>> > 12
>> decode(sign(to_number(to_char(timestamp,'SSSSS'))-21599),
>> > 13 -1, 86400, 0))/43200)
>> > 14 /
>> >
>> >Date Bet Sev 1 Sev 2 Sev 3
>> >----------- --- ---------- ---------- ----------
>> >01-DEC-1999 No 2 3 1
>> >02-DEC-1999 Yes 5 5 2
>> >02-DEC-1999 No 3 1 2
>> >
>> >3 rows selected.
>> >
>> >86400 is the number of seconds in a day and 21599 is 5h59mn59s.
>> >
>> >--
>> >Have a nice day
>> >Michel
>> >
>> >
>> ><vigi98_at_my-deja.com> a écrit dans le message :
>> 825ltc$2r5$1_at_nnrp1.deja.com...
>> >> Hi all,
>> >>
>> >> I'm not very good at doing SQL requests or PL/SQL scripts. Could you
>> >> solve the following problem :
>> >>
>> >> I've got three fields in a table : alarm_id,date-hour and severity.
>> >> Severity is an integer between 1 and 3.
>> >>
>> >> What I would like to obtain is what follows : for each period of 12h
>> >> (from 6AM to 6PM, then from 6PM to 6AM) I would have the number of
>> >> alarms per severity number.
>> >>
>> >> Typically, it would give a table with the following fields :
>> >>
>> >> date of the first part of the 12h period ; flag to indicate if the
hour
>> >> of the alarm is between 6AM and 6PM or not ; number of alarms of
>> >> severity 1 ; number of alarms of severity 2 ; number of alarms of
>> >> severity 3
>> >>
>> >> Thank you very much for your responses.
>> >>
>> >>
>> >> Sent via Deja.com http://www.deja.com/
>> >> Before you buy.
>> >
>> >
>>
>>
>
>
Received on Mon Dec 06 1999 - 07:46:55 CST
