[Oracle SQL] Seconden naar datum

[Refragmental]

Ik ben momenteel bezig met het maken van een query die moet gaan uitrekenen hoe lang een bepaald programma heeft gedraaid. Ik krijg nu een getal eruit dat het aantal dagen is, dit vermenigvuldig ik met 24*60*60 om het aantal seconden te krijgen.

Dit is leuk en aardig voor de programma's met een korte runtime, maar zodra we over de paar duizend seconden gaan wordt het een beetje minder goed leesbaar.

De query:

code:

select
(
to_date((108190+1900000)||lpad(102543,6,0), 'YYYYDDD:HH24:MI:SS')
-
to_date((108190+1900000)||lpad(94312,6,0), 'YYYYDDD:HH24:MI:SS')
)*24*60*60 "Time in seconds" from dual;

Even een korte uitleg... 108190 is de juliandate, hier moet 1900000 bij worden opgeteld om tot het volgende te komen... 2008190, dit betekent de 190ste dag van 2008.
Het 2e getal 102543 en 94312 zijn de tijdens... lees dus als 10:25:43 en 9:43:12, bij die laatste doe ik lpad om zodoende te garanderen dat ik altijd een evenlange cijferreeks heb. Dit is nodig vanwege de concatinate "||". Die moet altijd een getal opleveren die 13 cijfers lang is.
In dit geval krijg ik dus de getallen 2008190102543 en 2008190094312.
Met 'YYYYDDD:HH24:MI:SS' vertaalt zich dit naar 08/07/2008 10:25:43 en 08/07/2008 9:43:12.

Door deze 2 waarden van elkaar af te trekken krijg ik een waarde die aangeeft hoe lang van begin tot eind het programma heeft gedraaid.

Dit komt nu dus in aantal dagen eruit... daarover doe ik de berekening *24*60*60 om uit te komen op aantal seconden. In dit geval 2551 seconden.
Deze waarde wil ik echter op de volgende manier weergeven:
000 00:42:31 (DDD HH24:MI:SS), dit is namelijk veel beter leesbaar voor de managers
42 minuut 31s leest namelijk beter dan 2551 seconden.

Ik heb werkelijk al vanalles geprobeerd maar het wil mij maar niet lukken om van het aantal seconden naar deze format te gaan.

Mijn vraag dus. Is hier een makkelijk functie voor of is dit op één of andere manier mogelijk?

[RobIII]

En dit staat in SEA omdat...
Waar hoort mijn topic?
SEA >> PRG

En wat betreft die weergave: waarom laat jedat niet aan je presentatielaag over?

[ Voor 23% gewijzigd door RobIII op 14-07-2008 15:36 ]

[Refragmental]

RobIII schreef op maandag 14 juli 2008 @ 15:33:
En dit staat in SEA omdat...
Waar hoort mijn topic?
SEA >> PRG

En wat betreft die weergave: waarom laat jedat niet aan je presentatielaag over?

Omdat de presentatielaag (in dit geval Excel) er ook niet mee overweg kan.
Ik kan in ieder geval geen optie vinden om 2551 als 00:43:31, of zelfs 43m 31s weer te geven.

[justmental]

Alles voor de komma/punt is je aantal dagen, alles daarachter kun je omrekenen naar uren en minuten op dezelfe manier dat je naar seconden gaat.
Eventueel kun je ook zoiets doen:
select to_char(to_date('2551','sssss'),'hh24:mi:ss') from dual

[RobIII]

Refragmental schreef op maandag 14 juli 2008 @ 15:43:
[...]


Omdat de presentatielaag (in dit geval Excel) er ook niet mee overweg kan.
Ik kan in ieder geval geen optie vinden om 2551 als 00:43:31, of zelfs 43m 31s weer te geven.

Zet in een cel de formule

code:

=TIJD(0;0;2551)

en weergave op uu:mm:ss

[ Voor 8% gewijzigd door RobIII op 14-07-2008 15:46 ]

[Refragmental]

justmental schreef op maandag 14 juli 2008 @ 15:43:
Alles voor de komma/punt is je aantal dagen, alles daarachter kun je omrekenen naar uren en minuten op dezelfe manier dat je naar seconden gaat.
Eventueel kun je ook zoiets doen:
select to_char(to_date('2551','sssss'),'hh24:mi:ss') from dual

Ja dat werk wel... alleen zodra je meer dan 24 uur krijgt, dan loop je tegen problemen op.

code:

select to_char(to_date(86399,'SSSSS'),'HH24:MI:SS') from dual;

Die werkt wel nog... maar zodra je 86400 invoert dan krijg je al een foutmelding.
Er zijn hier programma's die soms dagen draaien, die vallen op deze manier buiten de boot, en dat is niet geheel gewenst.

[justmental]

Refragmental schreef op maandag 14 juli 2008 @ 15:49:
Ja dat werk wel... alleen zodra je meer dan 24 uur krijgt, dan loop je tegen problemen op.

code:

select to_char(to_date(86399,'SSSSS'),'HH24:MI:SS') from dual;

Die werkt wel nog... maar zodra je 86400 invoert dan krijg je al een foutmelding.
Er zijn hier programma's die soms dagen draaien, die vallen op deze manier buiten de boot, en dat is niet geheel gewenst.

Ik ga hem niet helemaal uitkauwen voor je.
Voor de komma zijn de dagen, na de komma kun je met deze truc doen.
En anders per onderdeel (dagen/uren/minuten/seconden) uitrekenen.

[Refragmental]

justmental schreef op maandag 14 juli 2008 @ 15:56:
[...]

Ik ga hem niet helemaal uitkauwen voor je.
Voor de komma zijn de dagen, na de komma kun je met deze truc doen.
En anders per onderdeel (dagen/uren/minuten/seconden) uitrekenen.

Oh op die fiets... ik ga eens kijken wat ik even snel in elkaar kan zetten.
Bedankt

Moet alleen kijken hoe ik het getal... bijvoorbeeld 0,0016732 op kan splitsen.

[Sepio]

Dit hoef je dit niet handmatig te doen.
Er bestaat hiervoor een functie in oracle.

SQL:

select numtodsinterval(123456, 'SECOND') from dual;

geeft als resultaat
+01 10:17:36.000000

Dus 1 dag, 10 uur, 17 minuten en 36 seconden.

Je kunt ook rechtstreeks twee datum velden van elkaar afhalen en het resultaat tonen.

SQL:

select numtodsinterval(sysdate - (sysdate-1.23456), 'DAY') from dual;

geeft als resultaat
+01 05:37:46.000000

[ Voor 34% gewijzigd door Sepio op 15-07-2008 13:00 ]

[Refragmental]

Sepio schreef op dinsdag 15 juli 2008 @ 10:58:
Dit hoef je dit niet handmatig te doen.
Er bestaat hiervoor een functie in oracle.

select numtodsinterval(123456, 'SECOND') from dual;
geeft als resultaat
+01 10:17:36.000000

Dus 1 dag, 10 uur, 17 minuten en 36 seconden.

Je kunt ook rechtstreeks twee datum velden van elkaar afhalen en het resultaat tonen.
select numtodsinterval(sysdate - (sysdate-1.23456), 'DAY') from dual;
geeft als resultaat
+01 05:37:46.000000

Wow!!!

Dit is PRECIES wat ik nodig heb!

Ik was al bezig met het klooien op de manier hierboven beschreven, maar de SQL werd echt een gigantisch onoverzichtelijk monster

Ik ga eens kijken hoe ik deze functie kan toepassen in mijn SQL.

[Refragmental]

Ok... mooi het is gelukt.

Alleen nu zit ik met een rare lange waarde.

Bijvoorbeeld
+01 00:34:12.000000

Die laatste nullen en de punt krijg ik makkelijk weg met een SUBSTR en INSTR functie.

Alleen krijg ik dan de volgende waarde.

+000000001 00:34:12
Nu zijn de achterste nullen weg... maar zijn er 8 nullen voor gekomen. Dat schiet niet op dus.

Hoe kan ik hier mooi +1 00:34:12 van krijgen?

[Sepio]

Als je het interval datatype eerst converteert naar varchar2 dan kun je wel zulke string operaties
gebruiken.

SQL:

select to_char(numtodsinterval(123456, 'SECOND')) from dual;

+000000001 10:17:36.000000000

Ik zou er ivm excel wel voor zorgen dat je bijvoorbeeld minimaal 3 posities voor de dag gebruikt (dus met voorloop nullen). Zo kan je de data blijven sorteren in excel.

Of als dit niet gewenst is kun je ook een reguliere expressie gebruiken om deze tekens te verwijderen.

SQL:

select 
    regexp_replace(
        '+000000001 10:17:36.000000000'
        ,'(\+|\.)0+' --Vervang de plus of punt gevolgd door 1 of meerdere nullen... 
        ,''          --...door niets.
    )
from dual;

resultaat: 1 10:17:36

[ Voor 4% gewijzigd door Sepio op 15-07-2008 13:03 ]

[Refragmental]

Sepio schreef op dinsdag 15 juli 2008 @ 12:58:
Als je het interval datatype eerst converteert naar varchar2 dan kun je wel zulke string operaties
gebruiken.

SQL:

select to_char(numtodsinterval(123456, 'SECOND')) from dual;

+000000001 10:17:36.000000000

Dit is wat ik nu dus krijg... met de SUBSTR en INSTR haal ik die laatste nullen en de punt weg.

Ik zou er ivm excel wel voor zorgen dat je bijvoorbeeld minimaal 3 posities voor de dag gebruikt (dus met voorloop nullen). Zo kan je de data blijven sorteren in excel.

2 of 3 karakters voor de dagen zijn inderdaad handig om te hebben, inderdaad vanwege het sorteren in excel.

Of als dit niet gewenst is kun je ook een reguliere expressie gebruiken om deze tekens te verwijderen.

SQL:

select 
    regexp_replace(
        '+000000001 10:17:36.000000000'
        ,'(\+|\.)0+' --Vervang de plus of punt gevolgd door 1 of meerdere nullen... 
        ,''          --...door niets.
    )
from dual;

resultaat: 1 10:17:36

Deze functie wil bij mij niet werken. Zegt dat ie de functie niet herkent.

[Sepio]

regexp_replace is een oracle 10 functie.

In plaats van substring en instr kun je ook gewoon een replace gebruiken.

SQL:

select replace(replace('+000000001 10:17:36.000000000','.000000000',null),'+000000',null) from dual;

Resultaat: 001 10:17:36

[Refragmental]

Sepio schreef op dinsdag 15 juli 2008 @ 13:37:
regexp_replace is een oracle 10 functie.

In plaats van substring en instr kun je ook gewoon een replace gebruiken.

SQL:

select replace(replace('+000000001 10:17:36.000000000','.000000000',null),'+000000',null) from dual;

Resultaat: 001 10:17:36

Ok dat verklaart mijn melding. Wij zitten momenteel op oracle 9. 10 gaan we binnenkort naartoe.

Is het ook mogelijk om in plaats van allemaal nullen te typen een wildcard voor cijfers te pakken?
Om 1 of andere reden geeft ie soms rare afrondingsfouten achter de comma waardoor alles op .999999 springt.

[Sepio]

Dit soort afrondingsfouten heb ik in oracle 10 nog niet gezien.
Je kunt in jouw geval beter kiezen om met substr te werken.
Instr is overbodig omdat de lengte al vast staat.

SQL:

select substr('+000000001 10:17:36.000000000',8,12) from dual;

Resultaat: 001 10:17:36
PS. Dit is eigenlijk zelfs beter dan de replace functie hierboven.

[ Voor 10% gewijzigd door Sepio op 15-07-2008 13:54 ]