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 :

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 :

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 :
1601563448

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()
2020-10-01 16:44:08
exécuté en 0 sec.
SELECT CURDATE()
CURDATE()
2020-10-01
exécuté en 0 sec.
SELECT CURTIME()
CURTIME()
16:44:08
exécuté en 0 sec.
SELECT UTC_TIMESTAMP()
UTC_TIMESTAMP()
2020-10-01 14:44:08
exécuté en 0 sec.
SELECT UTC_DATE()
UTC_DATE()
2020-10-01
exécuté en 0 sec.
SELECT UTC_TIME()
UTC_TIME()
14:44:08
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()
1601563448
exécuté en 0 sec.

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

SELECT FROM_UNIXTIME(1601563448)
FROM_UNIXTIME(1601563448)
2020-10-01 16:44:08
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('2020-10-01') as Annee
Annee
2020
exécuté en 0 sec.
SELECT MONTH('2020-10-01') as Mois
Mois
10
exécuté en 0 sec.
SELECT DAY('2020-10-01') as JourDuMois
JourDuMois
1
exécuté en 0 sec.
SELECT WEEKDAY('2020-10-01') as JourDeLaSemaine
JourDeLaSemaine
3
exécuté en 0 sec.
SELECT HOUR('2020-10-01 16:44:08') as Heures
Heures
16
exécuté en 0 sec.
SELECT MINUTE('2020-10-01 16:44:08') as Minutes
Minutes
44
exécuté en 0 sec.
SELECT SECOND('2020-10-01 16:44:08') as Secondes
Secondes
8
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 '2020-10-01') as AnnéeMois
AnnéeMois
202010
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 '2020-10-01' + INTERVAL 100 DAY as `Dans 100 jours nous serons le :`
Dans 100 jours nous serons le :
2021-01-09
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();
1601563448

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

echo date("Y-m-d H:i");
2020-10-01 16:44

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

echo strtotime("2020-10-01");
1601503200

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

echo microtime();
0.98908800 1601563448

echo microtime(true);
1601563448.9891

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 1 octobre 2020, la requête doit afficher 2020-9-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 1 octobre 2020, la requête doit nous afficher 2020-10-05.

Votre réponse n°6