Maîtriser les dates et heures dans MySQL : guide expert pour une gestion efficace

Les dates et heures sont essentielles pour organiser les données dans toute application logicielle. Manipuler ces éléments dans MySQL peut toutefois s'avérer complexe, notamment avec les fuseaux horaires, les additions ou soustractions de dates.

Découvrez les fonctions MySQL clés pour gérer facilement dates et heures dans votre base de données, en suivant les meilleures pratiques pour une fiabilité optimale.

Gérer les fuseaux horaires

Pour standardiser les opérations, stockez toujours les dates et heures en UTC. À chaque connexion MySQL, définissez le fuseau horaire UTC avec cette requête SQL :

SET TIME_ZONE = '+0:00';

Toutes les dates étant en UTC, les manipulations deviennent simples et prévisibles.

Pour convertir une date vers un autre fuseau, utilisez CONVERT_TZ(). Par exemple, PST (UTC-08:00) :

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

Résultat : 2021-02-04 13:47:23. Les arguments sont : date source, fuseau source ('+0:00' pour UTC), fuseau cible.

Ajouter ou soustraire des dates

Utilisez DATE_ADD() et DATE_SUB() pour ces opérations. Exemple : soustraire 2 semaines à la date actuelle :

SELECT DATE_SUB(NOW(), INTERVAL 2 WEEK);

Ajouter 3 jours à une date :

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

Format de l'intervalle : INTERVAL valeur unité (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR).

Exemple pratique : connexions des 45 dernières minutes :

SELECT * FROM logins WHERE login_date >= DATE_SUB(NOW(), INTERVAL 45 MINUTE);

Calculer la différence entre dates

DATEDIFF() renvoie le nombre de jours :

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

Pour les secondes, utilisez TO_SECONDS() :

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

Extraire des composants de dates

MySQL propose des fonctions pour isoler mois, heure, etc. Exemples :

SELECT MONTH('2021-02-11 15:27:52');  -- 2

SELECT HOUR(NOW());

SELECT DAYOFYEAR('2021-07-15 12:00:00');  -- 196

Liste complète (un seul argument : la date) :

- SECOND()    - MINUTE()   - HOUR()
- DAY()       - WEEK()     - MONTH()
- QUARTER()   - YEAR()
- DAYOFYEAR() - LAST_DAY() - DATE()
- TIME()      - TO_DAYS()  - TO_SECONDS()
- UNIX_TIMESTAMP()

Exemple : mois et année de création des utilisateurs :

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

Regrouper par période de date

Utilisez GROUP BY avec fonctions de date. Total des commandes 2020 par mois :

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

Préférez BETWEEN à YEAR() dans WHERE pour optimiser les index.

Ne vous perdez plus avec les dates

Avec ces techniques, manipulez dates et heures en UTC pour simplicité et précision. Idéal pour calculs, rapports ou extractions.

Débutant en SQL ? Consultez nos commandes SQL essentielles.