Hoe te Werken met Datums en Tijden in SQL

Introductie

Als u werkt met relationele databases en Structured Query Language (SQL), kunnen er momenten zijn waarop u moet werken met waarden die specifieke datums of tijden vertegenwoordigen. U moet bijvoorbeeld het totale aantal uren berekenen dat aan een bepaalde activiteit is besteed, of misschien moet u datums of tijden manipuleren met behulp van wiskundige operatoren en aggregatiefuncties om hun som of gemiddelde te berekenen.

In deze zelfstudie leert u hoe u datums en tijden kunt gebruiken in SQL. U begint met het uitvoeren van rekenkundige bewerkingen en het gebruiken van verschillende functies met datums en tijden met alleen de SELECT-verklaring. Vervolgens oefent u door vragen uit te voeren op voorbeeldgegevens, en leert u hoe u de CAST-functie kunt implementeren om de uitvoer gemakkelijker leesbaar te maken.

Vereisten

Om deze zelfstudie te voltooien, heeft u nodig:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL geïnstalleerd en beveiligd op de server. Volg onze handleiding Hoe MySQL te installeren op Ubuntu 20.04 om dit in te stellen. Deze handleiding gaat ervan uit dat u ook een niet-root MySQL-gebruiker hebt ingesteld, zoals beschreven in Stap 3 van deze handleiding.

Om te oefenen met het gebruik van datum en tijd in deze tutorial, heb je een database en een tabel nodig die zijn geladen met voorbeeldgegevens. Als je er geen klaar hebt om in te voegen, kun je de volgende Verbinding maken met MySQL en een voorbeelddatabase instellen sectie lezen om te leren hoe je een database en tabel kunt maken. In deze tutorial wordt voortdurend verwezen naar deze voorbeelddatabase en -tabel.

Verbinding maken met MySQL en een voorbeelddatabase instellen

Als je SQL-database op een externe server wordt uitgevoerd, maak dan verbinding met je server vanaf je lokale machine via SSH:

ssh sammy@your_server_ip

Vervolgens open je de MySQL-prompt, waarbij je sammy vervangt door jouw MySQL-gebruikersaccountinformatie:

mysql -u sammy -p

Maak een database met de naam datetimeDB:

CREATE DATABASE datetimeDB;

Als de database succesvol is aangemaakt, ontvang je de volgende uitvoer:

Output
Query OK, 1 row affected (0.01 sec)

Om de database datetimeDB te selecteren, voer je het volgende USE-statement uit:

USE datetimeDB;

Output
Database changed

Na het selecteren van de database, maak een tabel erin aan. Voor het voorbeeld van deze tutorial zullen we een tabel maken die de resultaten van twee hardlopers bij verschillende races gedurende een jaar bevat. Deze tabel zal de volgende zeven kolommen bevatten:

• race_id: toont waarden van het datatype int en dient als de primaire sleutel van de tabel, wat betekent dat elke waarde in deze kolom zal functioneren als een unieke identifier voor de respectievelijke rij.
• runner_name: gebruikt het datatype varchar met een maximum van 30 tekens voor de namen van de twee hardlopers, Bolt en Felix.
• race_name: bevat de typen races met het datatype varchar met een maximum van 20 tekens.
• start_day: gebruikt het datatype DATE om de datum van een specifieke race bij te houden op jaar-, maand- en dagbasis. Dit datatype volgt de volgende parameters: vier cijfers voor het jaar en maximaal twee cijfers voor de maand en dag (YYYY-MM-DD).
• start_time: vertegenwoordigt de starttijd van de race met het datatype TIME op basis van uren, minuten en seconden (HH:MM:SS). Dit datatype volgt een 24-uursklokformaat, zoals 15:00 voor het equivalent van 15:00 uur.
• total_miles: toont het totale aantal kilometers voor elke race met het gegevenstype decimal, aangezien veel van de totale kilometers per race geen gehele getallen zijn. In dit geval geeft decimal een precisie van drie met een schaal van één aan, wat betekent dat alle waarden in deze kolom drie cijfers kunnen hebben, waarvan één cijfer rechts van het decimaalteken staat.
• end_time: gebruikt het gegevenstype TIMESTAMP om de eindtijden van de hardlopers bij te houden. Dit gegevenstype combineert zowel datum als tijd in één string, en het formaat is een combinatie van die van DATE en TIME: (YYYY-MM-DD HH:MM:SS).

Maak de tabel door het CREATE TABLE commando uit te voeren:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

Voeg vervolgens wat voorbeeldgegevens toe aan de lege tabel:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Zodra u de gegevens hebt ingevoegd, bent u klaar om wat rekenkundige bewerkingen en functies met datum en tijd in SQL te oefenen.

Het gebruik van Rekenkundige bewerkingen met Datums en Tijden

In SQL kunt u datum- en tijdwaarden manipuleren met wiskundige expressies. Het enige wat nodig is, is de wiskundige operator en de waarden die u wilt berekenen.

Als voorbeeld, stel dat je één datum wilt vinden die een bepaald aantal dagen na een andere datum ligt. De volgende query neemt één datumwaarde (2022-10-05) en telt er 17 bij op om de waarde voor de datum zeventien dagen na de gespecificeerde datum in de query terug te geven. Let op dat dit voorbeeld 2022-10-05 specificeert als een DATE-waarde om ervoor te zorgen dat het DBMS het niet interpreteert als een tekenreeks of een ander gegevenstype:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

Zoals deze uitvoer aangeeft, is 17 dagen na 2022-10-05 2022-10-22, of 22 oktober 2022.

Als een ander voorbeeld, stel dat je het totale aantal uren tussen twee verschillende tijden wilt berekenen. Dit kun je doen door de twee tijden van elkaar af te trekken. Voor de volgende query is 11:00 de eerste tijdwaarde en 3:00 is de tweede tijdwaarde. Hier moet je aangeven dat beide TIME-waarden zijn om het verschil in uren terug te geven:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

Deze uitvoer vertelt je dat het verschil tussen 11:00 en 3:00 80000 is, ofwel 8 uur.

Oefen nu met het gebruik van rekenkundige bewerkingen op de datum- en tijdinformatie uit de voorbeeldgegevens. Voor de eerste query, bereken de totale tijd die de hardlopers nodig hadden om elke race te voltooien door end_time van start_time af te trekken:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Je zult merken dat de uitvoer in de total_time-kolom vrij lang en moeilijk leesbaar is. Later zullen we demonstreren hoe je de CAST-functie kunt gebruiken om deze gegevenswaarden om te zetten zodat ze duidelijker te lezen zijn.

Nu, als je alleen geïnteresseerd was in de prestaties van elke hardloper voor langere races, zoals de halve en hele marathons, kun je je gegevens bevragen om die informatie op te halen. Voor deze query trek je end_time af van start_time, en beperk je je resultaten door de WHERE-clausule te gebruiken om gegevens op te halen waar total_miles groter waren dan 12:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

In dit gedeelte heb je enkele berekeningen uitgevoerd met datums en tijden met de SELECT-verklaring en voor praktische doeleinden op de voorbeeldgegevens. Hierna zul je oefenen met queries met behulp van verschillende datum- en tijdfuncties.

Het gebruik van datum- en tijdfuncties en intervaluitdrukkingen

Er zijn verschillende functies die kunnen worden gebruikt om datum- en tijdwaarden te vinden en te manipuleren in SQL. SQL-functies worden meestal gebruikt om gegevens te verwerken of te manipuleren, en de beschikbare functies zijn afhankelijk van de SQL-implementatie. De meeste SQL-implementaties laten echter toe dat je de huidige datum en tijd vindt door te zoeken naar de waarden current_date en current_time.

Om bijvoorbeeld de datum van vandaag te vinden, is de syntaxis kort en bestaat alleen uit de SELECT-verklaring en de functie current_date zoals in het volgende:

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

Met dezelfde syntaxis kun je de huidige tijd vinden met de functie current_time:

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

Als je liever zowel datum als tijd in de output wilt opvragen, gebruik dan de functie current_timestamp:

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

Je kunt datum- en tijdfuncties zoals deze gebruiken binnen rekenkundige functies vergelijkbaar met de vorige sectie. Bijvoorbeeld, stel dat je wilt weten wat de datum was 11 dagen geleden vanaf de datum van vandaag. In dit geval zou je dezelfde syntaxisstructuur kunnen gebruiken als eerder om de functie current_date op te vragen en er vervolgens 11 van af te trekken om de datum van elf dagen geleden te vinden:

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

Zoals deze output aangeeft, was het 11 dagen geleden vanaf de current_date (op het moment van schrijven) 2022-02-06, ofwel 6 februari 2022. Probeer nu dezelfde bewerking uit te voeren, maar vervang current_date door de functie current_time:

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

Deze output toont aan dat wanneer je 11 aftrekt van de waarde van current_time, het 11 seconden aftrekt. De bewerking die je eerder uitvoerde met de functie current_date interpreteerde 11 als dagen, niet als seconden. Deze inconsistentie in hoe getallen worden geïnterpreteerd bij het werken met datum- en tijdfuncties kan verwarrend zijn. In plaats van dat je datum- en tijdwaarden moet manipuleren met rekenkundige bewerkingen zoals deze, laten veel databasebeheersystemen je explicieter zijn door het gebruik van INTERVAL-expressies.

INTERVAL-expressies stellen je in staat om te vinden wat de datum of tijd zou zijn voor of na een ingesteld interval vanaf een gegeven datum- of tijdexpressie. Ze moeten de volgende vorm hebben:

INTERVAL value unit

Om bijvoorbeeld de datum vijf dagen vanaf nu te vinden, zou je de volgende query kunnen uitvoeren:

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

Dit voorbeeld vindt de waarde van current_date en voegt vervolgens de intervaluitdrukking INTERVAL '5' DAY eraan toe. Dit geeft de datum 5 dagen vanaf nu terug:

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

Dit is veel minder ambigu dan de volgende query, die een vergelijkbare, zij het niet identieke, output produceert:

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

Merk op dat je ook intervallen van datums of tijden kunt aftrekken om waarden van voor de opgegeven datumwaarde te vinden:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

Welke eenheden beschikbaar zijn voor gebruik in INTERVAL-expressies, hangt af van je keuze van DBMS, hoewel de meeste opties zoals UUR, MINUUT en SECONDE zullen hebben:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

Nu je meer hebt geleerd over intervaluitdrukkingen en enkele datum- en tijdfuncties, ga je verder met oefenen met het werken met de voorbeeldgegevens die je hebt ingevoegd in de eerste stap.

Het gebruik van CAST en Aggregaatfuncties met Datum en Tijd

Herinner je je de derde voorbeeldquery uit de sectie Het gebruik van Rekenkunde met Datums en Tijden, toen je de volgende query uitvoerde om eindtijd van starttijd af te trekken om het totale aantal uren te berekenen dat elke hardloper per race heeft voltooid. De output resulteerde echter in een kolom met een zeer lange output, die volgt op het TIMESTAMP-gegevenstype dat in de tabel was ingesteld:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Omdat je een bewerking uitvoert met twee kolommen die verschillende gegevenstypen hebben (end_time die TIMESTAMP-waarden bevat en start_time die TIME-waarden bevat), weet de database niet welk gegevenstype moet worden gebruikt wanneer het resultaat van de bewerking wordt afgedrukt. In plaats daarvan worden beide waarden omgezet naar gehele getallen zodat de bewerking kan worden uitgevoerd, wat resulteert in de lange getallen in de kolom total_time.

Om deze gegevens duidelijker te maken om te lezen en te interpreteren, kun je de CAST-functie gebruiken om deze lange gehele getalwaarden om te zetten naar het gegevenstype TIME. Om dit te doen, begin met CAST en volg dit direct met een opening haakje, de waarden die je wilt converteren, en vervolgens het AS-sleutelwoord en het gegevenstype waarnaar je het wilt converteren.

De volgende query is identiek aan het vorige voorbeeld, maar gebruikt een CAST-functie om de kolom total_time naar het gegevenstype time te converteren:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST heeft de gegevenswaarden in deze uitvoer omgezet naar TIME, waardoor het veel gemakkelijker te lezen en te begrijpen is.

Nu, laten we een paar aggregatiefuncties gebruiken in combinatie met de CAST-functie om de kortste, langste en totale tijdresultaten van elke loper te vinden. Eerst, vraag naar de minimum (of kortste) hoeveelheid tijd besteed met de MIN-aggregatiefunctie. Opnieuw, zul je CAST willen gebruiken om de TIMESTAMP-gegevenswaarden naar TIME-gegevenswaarden om te zetten voor duidelijkheid. Let op dat bij het gebruik van twee functies zoals in dit voorbeeld, twee paren van haakjes nodig zijn en de berekening voor het totale aantal uren (eindtijd - starttijd) genest moet worden binnen één ervan. Voeg ten slotte een GROUP BY clausule toe om deze waarden te organiseren op basis van de runner_name-kolom zodat de output de raceresultaten van de twee lopers zal presenteren:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

Deze output toont de kortste looptijd van elke loper, in dit geval een minimum van zes minuten en 30 seconden voor Bolt, en zeven minuten en 15 seconden voor Felix.

Volgende, vind de langste looptijd van elke loper. Je kunt dezelfde syntaxis gebruiken als de vorige query, maar vervang deze keer MIN door MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

Deze output vertelt ons dat de langste looptijd van Bolt een totaal van drie uur, 23 minuten en 10 seconden was; en die van Felix was een totaal van vier uur, twee minuten en 10 seconden.

Nu laten we een query uitvoeren voor wat hoog-niveau informatie over het totale aantal uren dat elke hardloper aan het hardlopen besteedde. Voor deze query combineer de SUM aggregatiefunctie om de totale som van uren te vinden op basis van eindtijd - starttijd, en gebruik CAST om die gegevenswaarden om te zetten naar TIME. Vergeet niet GROUP BY op te nemen om de waarden te organiseren voor zowel de resultaten van de hardlopers:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Interessant is dat deze uitvoer de interpretatie voor MySQL laat zien, die eigenlijk de totale tijd berekent als gehele getallen. Als we deze resultaten als tijd lezen, wordt de totale tijd van Bolt opgesplitst in vijf uur, 28 minuten en 80 seconden; en de tijd van Felix wordt opgesplitst in zeven uur, 61 minuten en 49 seconden. Zoals je kunt zien, heeft deze tijdsverdeling geen zin, wat aangeeft dat het als een geheel getal wordt berekend en niet als tijd. Als je dit in een ander DBMS zou proberen, zoals PostgreSQL, zou dezelfde query er iets anders uitzien:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

In dit geval interpreteert de query in PostgreSQL de waarden als tijd en berekent deze ook als zodanig, zodat de resultaten van Felix worden opgesplitst in een totaal van 10 uur, één minuut en 44 seconden; en die van Bolt als zes uur, negen minuten en 20 seconden. Dit is een voorbeeld van hoe verschillende implementaties van DBMS gegevenswaarden anders kunnen interpreteren, zelfs als dezelfde query en dataset worden gebruikt.

Conclusie

Het begrijpen van hoe je datum en tijd in SQL gebruikt is handig bij het opvragen van specifieke resultaten zoals minuten, seconden, uren, dagen, maanden, jaren; of een combinatie van al deze. Bovendien zijn er veel functies beschikbaar voor datums en tijden die het gemakkelijker maken om bepaalde waarden te vinden, zoals de huidige datum of tijd. Hoewel deze handleiding alleen gebruik maakte van optel- en aftrekaritmetiek op datums en tijden in SQL, kun je datum- en tijdwaarden gebruiken met elke wiskundige expressie. Leer meer vanuit onze gids over wiskundige expressies en aggregatiefuncties en probeer ze uit met je datum- en tijdquery’s.