Effectief werken met datums en tijden in MySQL

Effectief werken met datums en tijden in MySQL

Datums en tijden zijn belangrijk, ze helpen de zaken georganiseerd te houden en vormen een integraal onderdeel van elke softwarebewerking.





Efficiënt ermee werken binnen de database kan soms verwarrend lijken, of het nu gaat om het werken in de verschillende tijdzones, het optellen/aftrekken van datums en andere bewerkingen.



Leer de verschillende MySQL-functies die beschikbaar zijn om eenvoudig datums / tijden in uw database te verwerken en te beheren.





Werken met tijdzones

Om de zaken gestandaardiseerd te houden, zou u alleen met datums/tijden in de UTC-tijdzone moeten werken. Elke keer dat u verbinding maakt met de MySQL-database, moet u de tijdzone naar UTC schakelen, wat kan worden gedaan met de volgende SQL-instructie:

SET TIME_ZONE = '+0:00'

Omdat alle datums nu in UTC worden opgeslagen, weet u altijd waar u mee werkt, waardoor de zaken eenvoudiger en eenvoudiger worden.



waar kan ik documenten afdrukken vanuit mijn e-mail?

Indien nodig kunt u gemakkelijk zet de tijdzone om van elke datetime / timestamp-waarde met de handy CONVERT_TZ() MySQL-functie. U moet eerst de offset kennen. PST aan de westkust van Noord-Amerika is bijvoorbeeld UTC -08:00, dus u kunt het volgende gebruiken:

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

Dit resulteert in 2021-02-04 13:47:23 wat precies klopt. De drie argumenten doorgegeven aan CONVERT_TZ() zijn eerst de datum/tijdstempel waarmee u begint (gebruik nu() voor de huidige tijd), de tweede is altijd '+0: 00' aangezien alle datums in de database naar UTC worden gedwongen, en de laatste de offset is waarnaar we de datum willen converteren.



Datums optellen/aftrekken

Vaak moet u datums optellen of aftrekken, bijvoorbeeld als u records van een week geleden moet ophalen of iets over een maand moet plannen.

Gelukkig heeft MySQL de uitstekende DATE_ADD() en DATE_SUB () functies die deze taak uiterst eenvoudig maken. U kunt bijvoorbeeld twee weken aftrekken van de huidige datum met de SQL-instructie:



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

Als u in plaats daarvan drie dagen aan een bestaande tijdstempel wilt toevoegen, gebruikt u:

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

Beide functies werken hetzelfde, het eerste argument is het tijdstempel waarmee u begint en het tweede argument is het interval dat moet worden opgeteld of afgetrokken. Het tweede argument heeft altijd dezelfde opmaak, beginnend met het woord interval gevolgd door een numerieke waarde en het interval zelf, dat een van de volgende kan zijn: seconde, minuut, uur, dag, week, maand, kwartaal, jaar.

Als u bijvoorbeeld alle aanmeldingen van de afgelopen 34 minuten wilt ophalen, kunt u een SQL-instructie gebruiken, zoals:

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

Zoals u kunt zien, zou dit alle records ophalen van de logins tabel met een inlogdatum groter dan de huidige tijd minus 45 minuten, oftewel de afgelopen 45 minuten.

Verschil tussen datums krijgen

Soms moet u weten hoeveel tijd er is verstreken tussen twee datums. U kunt eenvoudig het aantal dagen tussen twee verschillende datums krijgen met de DATEDIFF functie, zoals de onderstaande SQL-instructie:

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

De DATEDIFF functie heeft twee argumenten nodig, beide datum-/tijdstempels, en geeft het aantal dagen ertussen. Het bovenstaande voorbeeld toont het aantal verstreken dagen vanaf 15 december 2020 tot vandaag.

Om het aantal seconden tussen twee datums te krijgen, de TO_SECONDS() functie kan van pas komen, bijvoorbeeld:

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

Dit resulteert in het aantal seconden tussen de twee opgegeven datums.

Segmenten uit datums extraheren

Er zijn verschillende MySQL-functies waarmee u eenvoudig specifieke segmenten uit datums kunt extraheren, bijvoorbeeld als u alleen de maand, de dag van het jaar of het uur wilt. Hier zijn een paar voorbeelden van dergelijke functies:

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

De bovenstaande SQL-instructies zouden resulteren in: 02 , het huidige uur, en 196 aangezien 15 september de 196ste dag van het jaar is. Hier is een lijst van alle beschikbare datumextractiefuncties, elk met slechts één argument, waarvan de datum wordt geëxtraheerd:

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

Als u bijvoorbeeld alleen de maand en het jaar wilt ophalen waarin alle gebruikers zijn gemaakt, kunt u een SQL-instructie gebruiken zoals:

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

Dit zou alle records binnen de gebruikers tabel en laat het id #, de maand en het jaar zien waarin elke gebruiker is gemaakt.

Records groeperen op datumperiode

Een uitstekend gebruik van datumfuncties is de mogelijkheid om records te groeperen op datumperiode met behulp van GROEP OP binnen uw SQL-instructies. Misschien wilt u bijvoorbeeld het totale bedrag van alle bestellingen in 2020 gegroepeerd per maand bekijken. U kunt een SQL-statement gebruiken zoals:

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

Dit zou alle bestellingen ophalen die in het jaar 2020 zijn geplaatst, ze groeperen op de maand waarin ze zijn gemaakt en 12 records retourneren met het totale bestelde bedrag elke maand van het jaar.

Let op: voor betere indexprestaties is het altijd het beste om het gebruik van datumfuncties zoals: JAAR() binnen de WHERE-clausule van SQL-instructies, en gebruik in plaats daarvan de TUSSEN operator zoals weergegeven in het bovenstaande voorbeeld.

Nooit meer verward worden met datums

Met behulp van bovenstaande kennis kunt u nu efficiënt werken met, vertalen en bewerkingen uitvoeren in datums en tijden in een breed scala aan gebruikssituaties.

Vergeet niet om voor het gemak altijd UTC te gebruiken wanneer u met datums werkt, en gebruik de bovenstaande tips om datums efficiënt in uw software te beheren, of het nu gaat om het uitvoeren van eenvoudige berekeningen of het eenvoudig ophalen van rapporten gegroepeerd op datumperiodes.

Als je een beetje nieuw bent in SQL, zorg er dan voor dat je deze bekijkt essentiële SQL-opdrachten om uw SQL-gebruik te verbeteren.

Deel Deel Tweeten E-mail Professionele rapporten en documenten maken in Microsoft Word

Deze gids onderzoekt de elementen van een professioneel rapport en bespreekt de structurering, styling en finalisering van uw document in Microsoft Word.

Lees volgende Gerelateerde onderwerpen Over de auteur Matt Dizak(18 artikelen gepubliceerd) Meer van Matt Dizak

Abonneer op onze nieuwsbrief

Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!

Klik hier om je te abonneren