SQL : Gestion des dates sous MySQL.

Réponse AJAX

Les différents types de données concernant les dates

Les différents types de données concernant les dates sont :

DATE : la date (jour, mois, année), sur 3 octets.
DATETIME : la date (jour, mois, année) et l'heure (heures, minutes, secondes), sur 8 octets.
TIME : l'heure (heures, minutes, secondes), sur 3 octets.
TIMESTAMP : la date et l'heure, sur 4 octets. Le type TIMESTAMP est le seul type à pouvoir utiliser la valeur par défaut CURRENT_TIMESTAMP. Attention : le TIMESTAMP de MySQL ne ressemble pas au timestamp de Unix, mais plus au type DATETIME. Je déconseille de l'utiliser sous MySQL, au profit de DATETIME.
YEAR : année, sur 1 octets, auquel MySQL ajoute 1900 : de 1900 à 2155.

Puisque aucun type n'est prévu lorsqu'on veut stocker juste un jour de la semaine, juste un jour du mois, ou juste un mois, quel est le type le plus approprié qu'on utilisera pour ça ?

Votre réponse n°1

Les différents formats de représentation des dates

Le format chaîne de caractères

Une date au format chaîne de caractères s'écrit ainsi :

AAAA-MM-JJ : le format le plus utilisé.
AA-MM-JJ : l'année peut être sur deux chiffres au lieu de quatre. MySQL interprète alors de la façon suivante : l'intervalle 70 à 99 représente 1970 à 1999, 00 à 69 représente 2000 à 2069.
AAAAMMJJ, AAAAMMJJ : on peut supprimer le délimiteur, on peut même le remplacer par n'importe quel autre délimiteur.
AAAA-00-00 : le mois et le jour peuvent prendre la valeur zéro. La doc MySQL précise que c'est pratique lorsqu'on rentre une date d'anniversaire qu'on ne connaît pas encore entièrement.
AAAA-MM-JJ HH:MM:SS : dans les types appropriés (DATETIME et TIMESTAMP), on peut préciser jusqu'à la seconde près.
MM/JJ/AA : ce format, très courant dans les autres SGBDR (notamment ceux de Microsoft), n'est pas géré par MySQL.

Le Timestamp Unix

Le Timestamp Unix représente le nombre de secondes écoulées depuis le 1er janvier 1970 à 00h00. Par exemple, voici le timestamp actuel :
1686172678

Bien qu'il existe des fonctions (MySQL et PHP) pour afficher et manipuler ce format, aucun type de données MySQL ne correspond à ce format (même pas le type TIMESTAMP).

Le format standard SQL

En SQL, le format standard est : #MM/JJ/AA#.

Ce format est utilisable par exemple sous Access, SQL Server ou Oracle. Mais MySQL ne le gère pas du tout.

Les fonctions MySQL pour renvoyer la date courante

Les fonctions pour renvoyer la date courante sont (UTC signifie Coordinated Universal Time) :

SELECT NOW()

NOW()

2023-06-07 23:17:58

exécuté en 0 sec.

SELECT CURDATE()

CURDATE()

2023-06-07

exécuté en 0 sec.

SELECT CURTIME()

CURTIME()

23:17:58

exécuté en 0 sec.

SELECT UTC_TIMESTAMP()

UTC_TIMESTAMP()

2023-06-07 21:17:58

exécuté en 0 sec.

SELECT UTC_DATE()

UTC_DATE()

2023-06-07

exécuté en 0 sec.

SELECT UTC_TIME()

UTC_TIME()

21:17:58

exécuté en 0 sec.

Conversion entre le format chaîne de caractères et le format Timestamp Unix

Convertir une chaîne en timestamp

Pas trouvé : ceux qui trouvent sont priés de le rajouter en commentaire en bas de cette page

Convertir un Timestamp Unix en chaîne de caractères

La fonction MySQL pour renvoyer le Timestamp Unix est :

SELECT UNIX_TIMESTAMP()

UNIX_TIMESTAMP()

1686172678

exécuté en 0 sec.

La fonction MySQL pour convertir un Timestamp Unix en chaîne de caractères est :

SELECT FROM_UNIXTIME(1686172678)

FROM_UNIXTIME(1686172678)

2023-06-07 23:17:58

exécuté en 0 sec.

Cette fonction reçoit un deuxième paramètre, optionnel, pour préciser le format de la chaîne retournée.

Les fonctions pour extraire l'année, le mois, etc

Les fonctions pour extraire une partie de la date sont :

SELECT YEAR('2023-06-07') as Annee

Annee

2023

exécuté en 0 sec.

SELECT MONTH('2023-06-07') as Mois

Mois

exécuté en 0 sec.

SELECT DAY('2023-06-07') as JourDuMois

JourDuMois

exécuté en 0 sec.

SELECT WEEKDAY('2023-06-07') as JourDeLaSemaine

JourDeLaSemaine

exécuté en 0 sec.

SELECT HOUR('2023-06-07 23:17:58') as Heures

Heures

exécuté en 0 sec.

SELECT MINUTE('2023-06-07 23:17:58') as Minutes

Minutes

exécuté en 0 sec.

SELECT SECOND('2023-06-07 23:17:58') as Secondes

Secondes

exécuté en 0 sec.

La fonction extract permet d'extraire plusieurs valeurs en une seule fois. Par exemple, pour extraire l'année et le mois :

SELECT EXTRACT(YEAR_MONTH FROM '2023-06-07') as AnnéeMois

AnnéeMois

202306

exécuté en 0 sec.

Les intervalles de dates

Les fonctions

Certaines fonctions MySQL permettent de manipuler très simplement les intervalles de dates. Par exemple, quel jour serons-nous dans 100 jours ?

SELECT '2023-06-07' + INTERVAL 100 DAY as `Dans 100 jours nous serons le :`

Dans 100 jours nous serons le :

2023-09-15

exécuté en 0 sec.

Exemple d'utilisation

Exemple : quels sont les réponses aux TP données depuis moins d'une semaine par les BTS SIO 1 ? select concat(usr_first_name, ' ', usr_name) as nom, tp_date, tp_rep
FROM user, cours_tp_rep
WHERE usr_id = tp_usr_id
AND usr_special & 4
AND tp_date > NOW() - INTERVAL 7 DAY

Le BETWEEN

Un magasin a fait une promotion sur le produit n°346, du 10 décembre au 15 décembre. Il veut savoir combien d'achats il y a eu pendant cette période :
SELECT COUNT(achats) as nbAchats FROM Achete WHERE numProduit = 346 AND date BETWEEN '2011-12-10' AND '2011-12-15'

Le BETWEEN ne fonctionne pas qu'avec les dates, mais aussi avec les valeurs numériques et les chaînes quelconques.

Quelques fonctions PHP pour manipuler les dates

time() : donne le Timestamp Unix courant

echo time();
1686172678

date() : transforme un Timestamp Unix en chaîne de caractères

echo date("Y-m-d H:i");
2023-06-07 23:17

strtotime() : transforme une chaîne de caractères en Timestamp Unix

echo strtotime("2023-06-07");
1686088800

microtime() : affiche le Timestamp Unix avec une précision de l'ordre de la microseconde

echo microtime();
0.55268600 1686172678

echo microtime(true);
1686172678.5527

C'est très pratique pour chonométrer le temps d'exécution de nos programmes ou de nos requêtes.

Exercices

Quels sont les clients (Prénom, Nom) qui ont acheté au moins un produit l'année dernière ?

Votre réponse n°2

Quels sont les clients (Prénom, Nom) qui ont acheté au moins un produit depuis moins de 180 jours ?

Votre réponse n°3

Quels sont les clients qui n'ont encore rien acheté ce mois-ci ?

Votre réponse n°4

Ecrivez une requête SQL qui affiche le premier jour du mois précédent. Par exemple, si nous sommes le 7 juin 2023, la requête doit afficher 2023-5-01 (cette réponse est éligible à la qualité de réponse parfaite si elle fonctionne même en janvier).

Votre réponse n°5

Ecrivez une requête SQL qui affiche la date de lundi prochain. Par exemple, si nous sommes le 7 juin 2023, la requête doit nous afficher 2023-06-12.

Votre réponse n°6