Hvordan arbeide effektivt med datoer og tider i MySQL

Hvordan arbeide effektivt med datoer og tider i MySQL

Datoer og tider er viktige, de hjelper til med å holde ting organisert og er et integrert aspekt ved enhver programvareoperasjon.





Noen ganger kan det virke forvirrende å jobbe effektivt med dem i databasen, enten det fungerer på tvers av de forskjellige tidssonene, legge til / trekke fra datoer og andre operasjoner.



Lær de forskjellige MySQL -funksjonene som er tilgjengelige for enkelt å håndtere og administrere datoer / klokkeslett i databasen.





Arbeider med tidssoner

For å holde ting standardisert bør du bare jobbe med datoer / klokkeslett i UTC -tidssonen. Hver gang du oppretter en forbindelse til MySQL -databasen, bør du bytte tidssonen til UTC, noe som kan gjøres med følgende SQL -setning:

SET TIME_ZONE = '+0:00'

Siden alle datoer nå blir lagret i UTC, vet du alltid hva du jobber med, noe som gjør ting mer forenklet og rett frem.



Hvordan laste ned YouTube -videoer til kamerarullen

Når det er nødvendig kan du enkelt konvertere tidssonen av hvilken som helst datetime / tidsstempelverdi med hendig CONVERT_TZ () MySQL -funksjon. Du må vite forskyvningen først, for eksempel er PST på vestkysten av Nord -Amerika UTC -08: 00, så du kan bruke:

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

Dette resulterer i 2021-02-04 13:47:23 som er helt riktig. De tre argumentene gikk videre til CONVERT_TZ () er først datoen / tidsstemplet du starter med (bruk nå () for nåværende tid), vil den andre alltid være '+0: 00' siden alle datoer er tvunget til UTC i databasen, og den siste er forskyvningen vi ønsker å konvertere datoen til.



Legg til / trekk fra datoer

Mange ganger må du legge til eller trekke fra datoer, for eksempel hvis du trenger å hente poster fra en uke siden, eller planlegge noe om en måned fra nå.

Heldigvis har MySQL den utmerkede DATE_ADD () og DATE_SUB () funksjoner som gjør denne oppgaven ekstremt enkel. For eksempel kan du trekke to uker fra gjeldende dato med SQL -setningen:



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

Hvis du i stedet ønsket å legge til tre dager i et eksisterende tidsstempel, ville du bruke:

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

Begge funksjonene fungerer på samme måte, det første argumentet er tidsstempelet du starter med, og det andre argumentet er intervallet som skal legges til eller trekkes fra. Det andre argumentet er alltid formatert det samme som begynner med ordet intervall etterfulgt av en numerisk verdi og selve intervallet, som kan være et av følgende: sekund, minutt, time, dag, uke, måned, kvartal, år.

For et annet eksempel, hvis du ønsket å hente alle pålogginger som skjedde de siste 34 minuttene, kan du bruke en SQL -setning som:

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

Som du kan se, vil dette hente alle poster fra pålogginger tabell med en påloggingsdato større enn gjeldende tid minus 45 minutter, eller med andre ord, de siste 45 minuttene.

Få forskjell på datoer

Noen ganger må du få hvor lang tid som har gått mellom to datoer. Du kan enkelt få antall dager mellom to forskjellige datoer med DATEDIFF funksjon, for eksempel SQL -setningen nedenfor:

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

De DATEDIFF funksjon tar to argumenter, som begge er dato / tidsstempler og gir antall dager mellom dem. Eksemplet ovenfor viser antall dager som har gått fra 15. desember 2020 til i dag.

For å få antall sekunder mellom to datoer, TO_SECONDS () funksjonen kan komme godt med, for eksempel:

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

Dette vil resultere i antall sekunder mellom de to datoene som er oppgitt.

Trekk ut segmenter fra datoer

Det er forskjellige MySQL -funksjoner som lar deg enkelt trekke ut bestemte segmenter fra datoer, for eksempel hvis du bare ville ha måneden, dagen i året eller timen. Her er noen eksempler på slike funksjoner:

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

Ovennevnte SQL -setninger vil resultere i 02 , gjeldende time og 196 som 15. september er den 196. dagen i året. Her er en liste over alle tilgjengelige datoekstraksjonsfunksjoner, som bare tar ett argument, og datoen hentes fra:

- 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)

For eksempel, hvis du kanskje bare ville hente måneden og året som alle brukerne ble opprettet, kan du bruke en SQL -setning som:

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

Dette vil hente alle poster i brukere tabellen og vis ID#, måned og år hver bruker ble opprettet i.

Gruppering av poster etter datoperiode

En utmerket bruk av datofunksjoner er muligheten til å gruppere poster etter datoperiode GRUPPE AV i dine SQL -setninger. For eksempel vil du kanskje trekke det totale beløpet for alle bestillinger i 2020 gruppert etter måned. Du kan bruke en SQL -setning som:

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);

Dette vil hente alle bestillinger som ble lagt inn i år 2020, gruppere dem etter måneden de ble opprettet, og returnere 12 poster som viser det totale beløpet som er bestilt hver måned i året.

Vær oppmerksom på at for bedre indeksytelse er det alltid best å unngå å bruke datofunksjoner som f.eks ÅR() innenfor WHERE -leddet i SQL -setninger, og bruk i stedet MELLOM operatør som vist i eksemplet ovenfor.

Aldri bli forvirret med datoer igjen

Ved å bruke kunnskapen ovenfor kan du nå effektivt arbeide med, oversette og utføre operasjoner i datoer og klokkeslett i et bredt spekter av brukstilfeller.

Husk å alltid bruke UTC når du arbeider med datoer for enkelhets skyld, og bruk tipsene ovenfor for å effektivt administrere datoer i programvaren din, enten det er å fullføre enkle beregninger eller enkelt trekke rapporter gruppert etter datoperioder.

Hvis du er litt ny i SQL, må du sjekke ut disse viktige SQL -kommandoer for å forbedre din SQL -bruk.

Dele Dele kvitring E -post Hvordan lage profesjonelle rapporter og dokumenter i Microsoft Word

Denne veiledningen undersøker elementene i en profesjonell rapport og gjennomgår strukturering, styling og sluttbehandling av dokumentet ditt i Microsoft Word.

Les neste Relaterte temaer Om forfatteren Matt Dizak(18 artikler publisert) Mer fra Matt Dizak

Abonner på vårt nyhetsbrev

Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!

Klikk her for å abonnere