Datum och tider är viktiga, de hjälper till att hålla ordning på saker och ting och är en integrerad del av all programvaruoperation.
Att effektivt arbeta med dem i databasen kan ibland verka förvirrande, oavsett om det gäller att arbeta över de olika tidszonerna, lägga till/subtrahera datum och andra operationer.
Lär dig de olika MySQL-funktionerna som är tillgängliga för att enkelt hantera och hantera datum/tider i din databas.
Arbeta med tidszoner
För att hjälpa till att hålla saker och ting standardiserade bör du aldrig arbeta med datum/tider i UTC-tidszon. Varje gång du upprättar en anslutning till MySQL-databasen bör du byta tidszon till UTC, vilket kan göras med följande SQL-sats:
SET TIME_ZONE = '+0:00'
Eftersom alla datum nu kommer att sparas i UTC vet du alltid vad du arbetar med, vilket gör saker och ting mer förenklade och okomplicerade.
När det behövs kan du enkelt konvertera tidszonen för valfritt datum/tidsstämpelvärde med den praktiska CONVERT_TZ() MySQL-funktion. Du måste känna till offset först, till exempel är PST på Nordamerikas västkust UTC -08:00, så du kan använda:
SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');
Detta resulterar i 2021-02-04 13:47:23 vilket är helt korrekt. De tre argumenten övergick till CONVERT_TZ() är först datum/tidsstämpeln du börjar med (använd now() för aktuell tid), den andra kommer alltid att vara ‘+0:00’ eftersom alla datum tvingas till UTC i databasen, och den sista är den offset vi vill konvertera datumet till.
Lägg till/subtrahera datum
Många gånger behöver du lägga till eller dra ifrån datum, till exempel om du behöver hämta poster från en vecka sedan, eller schemalägga något om en månad.
Tack och lov har MySQL det utmärkta DATE_ADD() och DATE_SUB() funktioner som gör denna uppgift extremt enkel. Till exempel kan du subtrahera två veckor från det aktuella datumet med SQL-satsen:
SELECT DATE_SUB(now(), interval 2 week);
Om du istället ville lägga till tre dagar till en befintlig tidsstämpel, skulle du använda:
SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);
Båda funktionerna fungerar likadant, det första argumentet är tidsstämpeln du börjar med, och det andra argumentet är intervallet att addera eller subtrahera. Det andra argumentet formateras alltid på samma sätt som börjar med ordet intervall följt av ett numeriskt värde och själva intervallet, som kan vara något av följande: sekund, minut, timme, dag, vecka, månad, kvartal, år.
Till exempel, om du vill hämta alla inloggningar som har skett under de senaste 34 minuterna kan du använda en SQL-sats som:
SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);
Som du kan se skulle detta hämta alla poster från inloggningar tabell med ett inloggningsdatum som är större än den aktuella tiden minus 45 minuter, eller med andra ord, de senaste 45 minuterna.
Få skillnaden mellan datum
Ibland behöver du ta reda på hur lång tid som har gått mellan två dejter. Du kan enkelt få antalet dagar mellan två olika datum med DATUMDIFF funktion, som nedanstående SQL-sats:
SELECT DATEDIFF(now(), '2020-12-15');
De DATUMDIFF funktion tar två argument, som båda är datum/tidsstämplar och anger antalet dagar mellan dem. Exemplet ovan visar antalet dagar som förflutit från den 15 december 2020 till idag.
För att få antalet sekunder mellan två datum, TO_SECONDS() funktion kan komma till användning, till exempel:
SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');
Detta kommer att resultera i antalet sekunder mellan de två angivna datumen.
Det finns olika MySQL-funktioner som låter dig enkelt extrahera specifika segment från datum, till exempel om du bara vill ha månaden, dagen på året eller timmen. Här är några exempel på sådana funktioner:
SELECT MONTH('2021-02-11 15:27:52');
SELECT HOUR(now());
SELECT DAYOFYEAR('2021-07-15 12:00:00');
Ovanstående SQL-satser skulle resultera i 02, aktuell timme och 196 eftersom den 15 september är den 196:e dagen på året. Här är en lista över alla tillgängliga datumextraktionsfunktioner, var och en tar bara ett argument, datumet som extraheras från:
- 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)
Till exempel, om du kanske bara ville hämta månaden och året som alla användare skapades, kan du använda en SQL-sats som:
SELECT id, MONTH(created_at), YEAR(created_at) FROM users;
Detta skulle hämta alla poster inom användare tabell och visa id#, månad och år varje användare skapades i.
Gruppera poster efter datumperiod
En utmärkt användning av datumfunktioner är möjligheten att gruppera poster efter datumperiod med hjälp av GRUPP AV i dina SQL-satser. Till exempel kanske du vill dra det totala beloppet för alla beställningar under 2020 grupperade efter månad. Du kan använda en SQL-sats 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);
Detta skulle hämta alla beställningar som lagts under år 2020, gruppera dem efter den månad de skapades och returnera 12 poster som visar det totala beloppet beställda varje månad under året.
Observera att för bättre indexprestanda är det alltid bäst att undvika att använda datumfunktioner som t.ex ÅR() inom WHERE-satsen i SQL-satser, och använd istället MELLAN operatör som visas i exemplet ovan.
Förväxlas aldrig med datum igen
Med hjälp av ovanstående kunskap kan du nu effektivt arbeta med, översätta och utföra operationer i datum och tider i ett brett spektrum av användningsfall.
Kom ihåg att alltid använda UTC när du arbetar med datum för enkelhetens skull, och använd ovanstående tips för att effektivt hantera datum i din programvara, oavsett om det är för att göra enkla beräkningar eller enkelt dra rapporter grupperade efter datumperioder.
Om du är lite ny på SQL, se till att kolla in dessa viktiga SQL-kommandon för att förbättra din SQL-användning.
Om författaren
Matt Dizak (18 artiklar publicerade)
Mer från Matt Dizak
Prenumerera på vårt nyhetsbrev
Gå med i vårt nyhetsbrev för tekniska tips, recensioner, free e-böcker och exklusiva erbjudanden!
Klicka här för att prenumerera
