Re: SQL or PL/SQL

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 - 04:48:19 CST