Differenze tra tempi in SQL

DavidE

il 09 mar 2020, 15:03

9 risposte

DavidE

Utente Attivo

Iscritto da: ott, 2018

Messaggi:: 95

Differenze tra tempi in SQL

09 mar 2020, 15:25

Buonasera a tutti
è possibile calcolare le differenze tra tempi con una query SQL?
Per esempio:

Ora ----- Durata
18:05:22 00:00:03
18:05:25 00:00:02
18:05:27 00:00:02
18:05:29 null

La query dovrebbe selezionare il campo Ora dalla tabella di partenza e calcolare il campo Durata.
Sono riuscito con VBA a risolvere il problema, ma mi chiedevo se sia possibile farlo solo con SQL, e se sì, come.
Grazie

9 Risposte

@Alex
Super Famoso
Iscritto da
feb, 2012
Messaggi:
8907
Re: Differenze tra tempi in SQL
09 mar 2020, 15:31
Da VBA cosa hai fatto... o cosa intendi...?
Mostra un poco...
M
migliorabile
Super Famoso
Iscritto da
apr, 2013
Messaggi:
6197
Re: Differenze tra tempi in SQL
09 mar 2020, 15:52
Ni, piu' no che si.

una query puo' usare SOLTANTO i dati disponibili sul record corrente
NON PUO' usare dati presenti in record DISTINTI: NON HA il concetto di "IL RECORD DOPO"!

L'unico modo per farlo e' creare, medinate qualche accrocchio superaccrocchioso, una SUPERTABELLA che contenga COPPIE di record come SINGOLO record:

1^ + 2^
2^ + 3^
...

DavidE

Utente Attivo

Iscritto da: ott, 2018

Messaggi:: 95

Re: Differenze tra tempi in SQL

09 mar 2020, 16:13


Dim RsMacchine As DAO.Recordset
Dim RsTempi As DAO.Recordset
Dim Data1, Data2, Ora1, Ora2 As Date
Dim Macchina1, Macchina2, Stato1, Stato2 As Integer

    Set DB = CurrentDb()
    
    DB.Execute "delete * from tempi"
    
    Set RsMacchine = DB.OpenRecordset("select * from macchina order by data, ora")
    Set RsTempi = DB.OpenRecordset("tempi")
    RsMacchine.MoveFirst
    
    ' copio dati macchina su tabella tempi
    While Not RsMacchine.EOF
    
        RsTempi.AddNew
        RsTempi!Data = RsMacchine!Data
        RsTempi!Ora = RsMacchine!Ora
        RsTempi!Stato = RsMacchine!Stato
        RsTempi.Update
        RsMacchine.MoveNext
    
    Wend
    
    RsMacchine.Close
    RsTempi.Close
    
    ' ordino il recordset
    Set RsTempi = DB.OpenRecordset("select * from tempi order by data, ora, stato")
    RsTempi.MoveFirst
      
    ' calcolo tempi macchina
    While Not RsTempi.EOF
    
        Data1 = RsTempi!Data
        Ora1 = RsTempi!Ora
        RsTempi.MoveNext
        If RsTempi.EOF Then GoTo Fine
        Data2 = RsTempi!Data
        Ora2 = RsTempi!Ora
        RsTempi.MovePrevious
        RsTempi.Edit
        RsTempi!Durata = (DateValue(Data2) + TimeValue(Ora2)) - (DateValue(Data1) + TimeValue(Ora1))
        RsTempi.Update
        RsTempi.MoveNext

    Wend
    
Fine:
    If Not RsTempi Is Nothing Then RsTempi.Close
    DB.Close
    DoCmd.OpenForm "Durata"

@Alex
Super Famoso
Iscritto da
feb, 2012
Messaggi:
8907
Re: Differenze tra tempi in SQL
09 mar 2020, 16:35
In senso assoluto ti ha risposto migliorabile... in senso di possibilità puoi farlo scrivendo una funzione Public alla quale passi il campo di riferimento di ordine e recuperi la data del record successivo.

Questa funzione può essere richiamata dalla query che la chiamerà tante volte quanti sono i records... quindi va scritta in modo che impatti meno possibile nell'esecuzione...

Non so se sia meglio una tempo table o la function da scrivere invia e richiamare in SQL ma ti conviene valutarlo sia in termini di prestazioni, ma devi sapere scrivere bene il codice... e non intendo ordinato ma con concetti di riduzione tempi morti, oltretutto come sai non sarà scalabile.

P.s. il tuo codice è sicuramente poco ottimizzato avresti dovuto fare il.ciclo partendo dall'ultimo usando ORDER BY DESC ...
Sapendo che l'ultimo non richiede conteggio lo memorizzi ed entri nel loop dove calcoli la.differenza con il next e memorizzi il dato perni calcolo successivo, e non dal primo che ti costringe ad andare avanti/indietro... oppure scrivere 2 volte nella tabella... tanto per capire ...
D
DavidE
Utente Attivo
Iscritto da
ott, 2018
Messaggi:
95
Re: Differenze tra tempi in SQL
09 mar 2020, 17:04
Grazie Alex
userò VBA, che da quanto capisco è la scelta migliore.
Vedrò anche di applicare il tuo consiglio (in effetti non ci avevo pensato)

DavidE

Utente Attivo

Iscritto da: ott, 2018

Messaggi:: 95

Re: Differenze tra tempi in SQL

09 mar 2020, 17:18

@Alex ha scritto:
P.s. il tuo codice è sicuramente poco ottimizzato avresti dovuto fare il.ciclo partendo dall'ultimo usando ORDER BY DESC ...
Sapendo che l'ultimo non richiede conteggio lo memorizzi ed entri nel loop dove calcoli la.differenza con il next e memorizzi il dato perni calcolo successivo, e non dal primo che ti costringe ad andare avanti/indietro... oppure scrivere 2 volte nella tabella... tanto per capire ...

Credo tu suggerisca questo:

 
 ' ordino il recordset
    Set RsTempi = DB.OpenRecordset("select * from tempi order by data DESC, ora DESC, stato")
    RsTempi.MoveFirst

    ' calcolo tempi macchina
    While Not RsTempi.EOF
     
        Data1 = RsTempi!Data
        Ora1 = RsTempi!Ora
        RsTempi.MoveNext
        If RsTempi.EOF Then GoTo Fine
        Data2 = RsTempi!Data
        Ora2 = RsTempi!Ora
        RsTempi.Edit
        RsTempi!Durata = (DateValue(Data1) + TimeValue(Ora1)) - (DateValue(Data2) + TimeValue(Ora2))
        RsTempi.Update
        
    Wend

@Alex
Super Famoso
Iscritto da
feb, 2012
Messaggi:
8907
Re: Differenze tra tempi in SQL
09 mar 2020, 17:43
Il senso è quello io spsoterei fuori dal ciclo la memorizzazione del primo con un movente next ... in modo che il (While Not RsTempi.EOF) sia l'unico controllo... all'interno del while poi dopo aver scritto scambi il valore prima del movenext.
In questo modo non devi inserire un doppio controllo EOF.

Ora una riflessione... i dati temporali possono cambiare oppure una volta scritti non possono essere editato...?
In questo secondo caso perché non valuti di aggiungere un campo alla tabella principale per evitare la TEMP Table e memorizzi li la differenza ma non con il vba riciclando, ma in fase di INSERIMENTO NEW vai a valorizzare il record precedente.
In questo modo AZZERI i tempi di.esecuzione calcoli.
D
DavidE
Utente Attivo
Iscritto da
ott, 2018
Messaggi:
95
Re: Differenze tra tempi in SQL
09 mar 2020, 18:17
@Alex ha scritto:
Il senso è quello io spsoterei fuori dal ciclo la memorizzazione del primo con un movente next ... in modo che il (While Not RsTempi.EOF) sia l'unico controllo... all'interno del while poi dopo aver scritto scambi il valore prima del movenext.
In questo modo non devi inserire un doppio controllo EOF.
Grazie ancora per il supporto ma questo non l'ho capito. Metto il primo move next prima del while.
Poi però la differenza la devo fare col record precedente...

@Alex ha scritto:
Ora una riflessione... i dati temporali possono cambiare oppure una volta scritti non possono essere editato...?
In questo secondo caso perché non valuti di aggiungere un campo alla tabella principale per evitare la TEMP Table e memorizzi li la differenza ma non con il vba riciclando, ma in fase di INSERIMENTO NEW vai a valorizzare il record precedente.
In questo modo AZZERI i tempi di esecuzione calcoli.
In effetti non ho poi bisogno di modificare i tempi. Quindi posso aggiungere il campo durata alla tabella principale.
Ma poi non capisco come aggiornarlo senza ciclare con VBA. Insert non permette calcoli tra record.
Grazie

@Alex

Super Famoso

Iscritto da: feb, 2012

Messaggi:: 8907

Re: Differenze tra tempi in SQL

09 mar 2020, 18:38

Perchè tieni differenziati i campi Data ed ORA che poi li riunisci spero sia dato da impistazioni non dipendenti da te...

Per l'ottimizzazione del codice


   Dim dtPrev  As Date
    Set RsTempi = DB.OpenRecordset("select * from tempi order by data DESC, ora DESC, stato")
    RsTempi.MoveFirst
    dtPrev = DateValue(RsTempi!Data) + TimeValue(RsTempi!Ora)   
    RsTempi.MoveNext
    ' calcolo tempi macchina
    Do Until RsTempi.EOF
        RsTempi.Edit
        RsTempi!Durata = dtPrev-(DateValue(RsTempi!Data) + TimeValue(RsTempi!Ora)) 
        RsTempi.Update
        dtPrev = DateValue(RsTempi!Data) + TimeValue(RsTempi!Ora)
        RsTempi.MoveNext
    Loop
    RsTempi.Close
    Set RsTempi=nothing


Per l'ipotesi di gestirlo ui nfase di Inserimento...

Su Evento AFTERINSERT del Record, puoi memorizzare il Valore Data/Ora, poi spostarti al Record Precedente, andare in EDIT inserendo il calcolo, e poi ritornare al Record inserito...
Private Sub Form_AfetInsert()
   ' Verifica che ci siano i Valori di Data/Ora, altrimenti Esci
    Dim dtPrev  As Date
    dtPrev = DateValue(Me!Data) + TimeValue(Me!Ora)   
    With Me.RecordsetClone
        .MovePRevious
        .Edit
        .Fields("Durata") = dtPrev - (DateValue(!Data) + TimeValue(!Ora)) 
        .Update
        .Bookmark=Me.Bookmark
    End With
 End Sub

Il Codice l'ho scritto a braccio senza testarlo, ma solo per farti comprendere il senso tecnico, ciò detto dovrebbe essere corretto.

Devi accedere o registrarti per scrivere nel forum

9 risposte