Come lavorare in modo efficace con date e orari in MySQL

Come lavorare in modo efficace con date e orari in MySQL

Le date e gli orari sono importanti, aiutano a mantenere le cose organizzate e sono un aspetto integrante di qualsiasi operazione software.





Lavorare in modo efficiente con loro all'interno del database a volte può sembrare fonte di confusione, indipendentemente dal fatto che funzioni nei vari fusi orari, aggiungendo/sottraendo date e altre operazioni.



Scopri le varie funzioni MySQL disponibili per gestire e gestire facilmente date/orari all'interno del tuo database.





Lavorare con i fusi orari

Per aiutare a mantenere le cose standardizzate, dovresti lavorare solo con date / orari nel fuso orario UTC. Ogni volta che stabilisci una connessione al database MySQL, dovresti cambiare il fuso orario in UTC, cosa che può essere fatta con la seguente istruzione SQL:

SET TIME_ZONE = '+0:00'

Poiché tutte le date verranno ora salvate in UTC, saprai sempre con cosa stai lavorando, rendendo le cose più semplicistiche e dirette.



come guardare instagram in diretta su pc

Quando necessario puoi facilmente convertire il fuso orario di qualsiasi valore datetime / timestamp con il pratico CONVERT_TZ() Funzione MySQL. Devi prima conoscere l'offset, ad esempio, PST sulla costa occidentale del Nord America è UTC -08:00, quindi potresti usare:

SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

Questo risulta in 04/02/2021 13:47:23 che è esattamente corretto. I tre argomenti passati a CONVERT_TZ() sono prima la data e l'ora / timestamp con cui stai iniziando (usa now() per l'ora corrente), la seconda sarà sempre '+0: 00' poiché tutte le date sono forzate all'UTC nel database e l'ultimo è l'offset in cui desideriamo convertire la data.



Aggiungi / Sottrai date

Molte volte è necessario aggiungere o sottrarre date, ad esempio se è necessario recuperare i record di una settimana fa o pianificare qualcosa tra un mese.

Per fortuna MySQL ha l'eccellente DATA_AGGIUNGI() e DATE_SUB () funzioni che rendono questo compito estremamente facile. Ad esempio, puoi sottrarre due settimane dalla data corrente con l'istruzione SQL:



SELECT DATE_SUB(now(), interval 2 week);

Se invece volessi aggiungere tre giorni a un timestamp esistente, useresti:

SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Entrambe le funzioni funzionano allo stesso modo, il primo argomento è il timestamp con cui si inizia e il secondo argomento è l'intervallo da aggiungere o sottrarre. Il secondo argomento è sempre formattato allo stesso modo iniziando con la parola intervallo seguito da un valore numerico e dall'intervallo stesso, che può essere uno dei seguenti: secondo, minuto, ora, giorno, settimana, mese, trimestre, anno.

Per un altro esempio, se si desidera recuperare tutti gli accessi avvenuti negli ultimi 34 minuti, è possibile utilizzare un'istruzione SQL come:

SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Come puoi vedere, questo recupererebbe tutti i record dal accessi tabella con una data di accesso maggiore dell'ora corrente meno 45 minuti o, in altre parole, degli ultimi 45 minuti.

Ottieni la differenza tra le date

A volte è necessario ottenere quanto tempo è trascorso tra due date. Puoi facilmente ottenere il numero di giorni tra due date diverse con il DATEDIFF funzione, come la seguente istruzione SQL:

SELECT DATEDIFF(now(), '2020-12-15');

Il DATEDIFF La funzione accetta due argomenti, entrambi sono data/ora e fornisce il numero di giorni tra di loro. L'esempio sopra mostra il numero di giorni trascorsi dal 15 dicembre 2020 fino ad oggi.

Per ottenere il numero di secondi tra due date, il TO_SECONDS() la funzione può tornare utile, ad esempio:

SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Ciò si tradurrà nel numero di secondi tra le due date fornite.

Estrai segmenti dalle date

Esistono varie funzioni MySQL che ti consentono di estrarre facilmente segmenti specifici dalle date, ad esempio se volessi solo il mese, il giorno dell'anno o l'ora. Ecco alcuni esempi di tali funzioni:

SELECT MONTH('2021-02-11 15:27:52'); SELECT HOUR(now()); SELECT DAYOFYEAR('2021-07-15 12:00:00');

Le precedenti istruzioni SQL risulteranno in 02 , l'ora corrente e 196 poiché il 15 settembre è il 196° giorno dell'anno. Ecco un elenco di tutte le funzioni di estrazione della data disponibili, ognuna con un solo argomento, la data viene estratta da:

- SECOND()
- MINUTE()
- HOUR()
- DAY()
- WEEK() - Number 0 - 52 defining the week within the year.
- MONTH()
- QUARTER() - Number 1 - 4 defining the quarter of the year.
- YEAR()
- DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
- LAST_DAY() - The last day in the given month.
- DATE() - The date in YYYY-MM-DD format without the time.
- TIME() The time in HH:II:SS format without the date.
- TO_DAYS() - The number of days since A.D. 0.
- TO_SECONDS() - The number of seconds since A.D. 0.
- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Ad esempio, se si desidera recuperare solo il mese e l'anno in cui sono stati creati tutti gli utenti, è possibile utilizzare un'istruzione SQL come:

SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Questo recupererebbe tutti i record all'interno del utenti tabella e mostra l'id#, il mese e l'anno in cui ogni utente è stato creato.

Raggruppamento dei record per periodo di data

Un ottimo uso delle funzioni di data è la possibilità di raggruppare i record per periodo di data utilizzando RAGGRUPPARE PER all'interno delle istruzioni SQL. Ad esempio, potresti voler estrarre l'importo totale di tutti gli ordini nel 2020 raggruppati per mese. Potresti usare un'istruzione SQL come:

SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

Ciò recupererebbe tutti gli ordini effettuati nell'anno 2020, li raggrupperebbe in base al mese in cui sono stati creati e restituirebbe 12 record che mostrano l'importo totale ordinato ogni mese dell'anno.

Si prega di notare, per una migliore performance dell'indice, è sempre meglio evitare di utilizzare funzioni di data come ANNO() all'interno della clausola WHERE delle istruzioni SQL e utilizzare invece il TRA operatore come mostrato nell'esempio precedente.

Non essere mai più confuso con le date

Utilizzando le conoscenze di cui sopra, ora puoi lavorare, tradurre ed eseguire in modo efficiente operazioni in date e orari in un'ampia gamma di casi d'uso.

Ricorda di utilizzare sempre UTC quando lavori con le date per semplicità e utilizza i suggerimenti di cui sopra per gestire in modo efficiente le date all'interno del tuo software, sia che si tratti di completare semplici calcoli o di estrarre facilmente rapporti raggruppati per periodi di data.

Se sei un po' nuovo in SQL, assicurati di dare un'occhiata a questi comandi SQL essenziali per aiutarti a migliorare l'uso di SQL.

Condividere Condividere Tweet E-mail Come creare report e documenti professionali in Microsoft Word

Questa guida esamina gli elementi di un rapporto professionale e esamina la struttura, lo stile e la finalizzazione del documento in Microsoft Word.

Leggi Avanti Argomenti correlati Circa l'autore Matt Dizaki(18 Articoli Pubblicati) Altro da Matt Dizak

Iscriviti alla nostra Newsletter

Iscriviti alla nostra newsletter per suggerimenti tecnici, recensioni, ebook gratuiti e offerte esclusive!

Clicca qui per iscriverti