Fonctions de dates et d'heures

Fonctions de dates et d'heures

6.3 Fonctions à utiliser dans les clauses SELECT et WHERE
6 Référence du langage MySQL
Manuel de Référence MySQL 4.1 : Version Française

. Opérateurs et fonctions tout-types
. Fonctions de chaînes de caractères
. Fonctions numériques
->Fonctions de dates et d'heures
. Fonctions de transtypage
. Autres fonctions
. Fonctions avec la clause GROUP BY

6.3.4 Fonctions de dates et d'heures

Voir Types dates et heures pour une description détaillée des intervalles de validité de chaque type, ainsi que les formats valides de spécifications des dates et heures.

Voici un exemple d'utilisation des fonctions de date. La requête suivante sélectionne toutes les lignes dont la colonne date_col représente une date de moins de 30 jours :


mysql> SELECT quelquechose FROM nom_de_table
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK(date)

Retourne l'index du jour de la semaine : pour date ( 1 = Dimanche, 2 = Lundi, ... 7 = Samedi). Ces index correspondent au standart ODBC :


mysql> SELECT DAYOFWEEK('1998-02-03');
        -> 3

WEEKDAY(date)

Retourne l'index du jour de la semaine, avec la convertion suivante : date ( 0 = Lundi, 1 = Mardi, ... 6 = Dimanche) :


mysql> SELECT WEEKDAY('1997-10-04 22:23:00');
        -> 5
mysql> SELECT WEEKDAY('1997-11-05');
        -> 2

DAYOFMONTH(date)

Retourne le jour de la date date , dans un intervalle de 1 à 31 :


mysql> SELECT DAYOFMONTH('1998-02-03');
        -> 3

DAYOFYEAR(date)

Retourne le jour de la date date , dans un intervalle de 1 à 366 :


mysql> SELECT DAYOFYEAR('1998-02-03');
        -> 34

MONTH(date)

Retourne le numéro du mois de la date date , dans un intervalle de 1 à 12 :


mysql> SELECT MONTH('1998-02-03');
        -> 2

DAYNAME(date)

Retourne le nom du jour de la semaine, en anglais, de la date date :


mysql> SELECT DAYNAME("1998-02-05");
        -> 'Thursday'

MONTHNAME(date)

Retourne le nom du mois de la date date :


mysql> SELECT MONTHNAME("1998-02-05");
        -> 'February'

QUARTER(date)

Retourne le numéro du trimestre de la date date , dans un intervalle de 1 à 4 :


mysql> SELECT QUARTER('98-04-01');
        -> 2

WEEK(date)

WEEK(date,first)

Avec un seul argument, retourne le numéro de la semaine dans l'année de la date date , dans un intervalle de 0 à 53 (oui, il peut y avoir un début de semaine numéro 53), en considérant que Dimanche est le premier jour de la semaine. Avec deux arguments, la fonction WEEK() vous permet de spécifier si les semaines commencent le Dimanche ou le Lundi et la valeur retournée sera dans l'intervalle 0-53 ou bien 1-52 .Voici un tableau explicatif sur le fonctionnement du second argument :

Value	`Meaning`
0	La semaine comme le Dimanche et retourne une valeur dans l'intervalle 0-53
1	La semaine comme le Lundi et retourne une valeur dans l'intervalle 0-53
2	La semaine comme le Dimanche et retourne une valeur dans l'intervalle 1-53
3	La semaine comme le Lundi et retourne une valeur dans l'intervalle 1-53 (ISO 8601)


mysql> SELECT WEEK('1998-02-20');
        -> 7
mysql> SELECT WEEK('1998-02-20',0);
        -> 7
mysql> SELECT WEEK('1998-02-20',1);
        -> 8
mysql> SELECT WEEK('1998-12-31',1);
        -> 53

Note : Dans la version 4.0 de MySQL, WEEK(#,0) a été modifié pour être utilisé avec le calendrier USA.

Notez que si une semaine est la dernière semaine de l'année précédente, MySQL retournera 0 si vous n'utilisez pas 2 ou 3 comme argument optionnel :


mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0
mysql> SELECT WEEK('2000-01-01',2);
        -> 52

On peut prétendre que la fonction WEEK() de MySQL retourne 52 , vu que la date donnée est en fait, la 52 ème semaine de l'année 1999.

Nous avons décidé de retourner 0 au lieu de vouloir que la fonction retourne 'le numéro de la semaine dans une année donnée'. Ceci rend l'utilisation de la fonction WEEK() fiable une fois combinée avec d'autres fonctions qui extraient une partie de date à partir d'une date :


mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> 52

YEAR(date)

Retourne l'année de la date date , dans un intervalle de 1000 à 9999 :


mysql> SELECT YEAR('98-02-03');
        -> 1998

YEARWEEK(date)

YEARWEEK(date,first)

Retourne l'année et la semaine pour une date. Le second argument fonctionne exactement comme le second argument de la fonction WEEK() . Notez que l'année peut être différente de l'année de la date pouur la première et la dernière semaine de l'année :


mysql> SELECT YEARWEEK('1987-01-01');
        -> 198653

Notez que le numéro de la semaine est différent de la valeur retournée par la fonction WEEK() ( 0 ) pour un argument optionnel 0 ou 1, alors que la fonction WEEK() retourne une semaine dans une année donnée.

HOUR(time)

Retourne le nombre d'heures pour l'heure time , dans un intervalle de 0 à 23 :


mysql> SELECT HOUR('10:05:03');
        -> 10

MINUTE(time)

Retourne le nombre de minutes pour l'heure for time , dans un intervalle de 0 à 59 :


mysql> SELECT MINUTE('98-02-03 10:05:03');
        -> 5

SECOND(time)

Retourne le nombre de secondes pour l'heure time , dans un intervalle de 0 à 59 :


mysql> SELECT SECOND('10:05:03');
        -> 3

PERIOD_ADD(P,N)

Ajoute N mois à la période P (au format YYMM ou YYYYMM ). Retourne une valeur dans le format YYYYMM .

Notez que l'argument P n'est pas de type date :


mysql> SELECT PERIOD_ADD(9801,2);
        -> 199803

PERIOD_DIFF(P1,P2)

Retourne le nombre de mois entre les périodes P1 et P2 . P1 et P2 doivent être dans au format YYMM ou YYYYMM .Notez que les arguments P1 et P2 ne sont pas de type date :


mysql> SELECT PERIOD_DIFF(9802,199703);
        -> 11

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

ADDDATE(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type)

Ces fonctions effectuent des calculs sur les dates. Elles sont nouvelles depuis la version 3.22 de MySQL. ADDDATE() et SUBDATE() sont synonymes de DATE_ADD() and DATE_SUB() . Dans la version 3.23 de MySQL, vous pouvez utiliser les opérateurs + et - en lieu et place des fonctions DATE_ADD() et DATE_SUB() si l'expression de droite est une colonne de type date ou datetime. (Voir l'exemple suivant) date est une valeur de type DATETIME ou DATE , indiquant la date du début. expr est une expression spécifiant la valeur de l'intervalle à ajouter ou à soustraire depuis le début de la date. expr est une chaîne ; elle peut commencer par le signe '-' pour les intervalles négatifs. type est un mot clé indiquant comment l'expression doit être interprétée.

La fonction EXTRACT(type FROM date) retourne le 'type' d'intervalle pour la date.

La table suivante illustre la relation entre les arguments type et expr :

`type` value	Expected `expr` format
`SECOND`	`SECONDES`
`MINUTE`	`MINUTES`
`HOUR`	`HEURES`
`DAY`	`JOURS`
`MONTH`	`MOIS`
`YEAR`	`ANNES`
`MINUTE_SECOND`	`"MINUTES:SECONDES"`
`HOUR_MINUTE`	`"HEURES:MINUTES"`
`DAY_HOUR`	`"JOURS HEURES"`
`YEAR_MONTH`	`"ANNEES-MOIS"`
`HOUR_SECOND`	`"HEURES:MINUTES:SECONDES"`
`DAY_MINUTE`	`"JOURS HEURES:MINUTES"`
`DAY_SECOND`	`"JOURS HEURES:MINUTES:SECONDES"`

MySQL autorise n'importe quel signe de ponctuation comme délimiteur dans expr . Ceux qui sont présentés dans la table ci-dessus ne sont que des suggestions. Si l'argument date est une DATE et que votre calcul implique seulement les années ( YEAR ), les mois ( MONTH ), et les jours ( DAY ) (c'est-à-dire, pas d'heures), le résultat est une DATE . Sinon, le résultat est une valeur de type DATETIME :


mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
        -> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
        -> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
       -> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
    ->                 INTERVAL 1 SECOND);
        -> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
    ->                 INTERVAL 1 DAY);
        -> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
    ->                 INTERVAL "1:1" MINUTE_SECOND);
        -> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
    ->                 INTERVAL "1 1:1:1" DAY_SECOND);
        -> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
    ->                 INTERVAL "-1 10" DAY_HOUR);
        -> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
        -> 1997-12-02

Si vous spécifiez un intervalle de valeur qui est trop court (c'est-à-dire qu'il n'inclut pas toutes les valeurs auxquelles ont peu s'attendre pour le type type ), MySQL suppose que vous avez ignoré ces valeurs. Par exemple, si vous spécifiez un type de DAY_SECOND , la valeur expr doit avoir des jours, heures, minutes, secondes. Si vous spécifiez une valeur telle que "1:10" , MySQL supposera que les jours et les heures sont ignorés, et que la valeur fournit représente uniquement les minutes et les secondes. En d'autres termes, "1:10" DAY_SECOND s'interprète comme "1:10" MINUTE_SECOND . C'est comparable à la manière dont MySQL interprète les valeurs TIME qui représente une durée plutôt qu'une heure du jour.Notez que si vous ajoutez ou soustrayez à une date une valeur qui contient des heures, le résultat final sera automatiquement converti en DATETIME :


mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY);
       -> 1999-01-02
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);
       -> 1999-01-01 01:00:00

Si vous utilisez des dates incorrectes, le résultat sera NULL . Si vous ajoutez des MONTH , YEAR_MONTH , ou YEAR , et que le résultat dépasse le nombre de jour dansle mois, ce nombre de jour sera ramené au maximun acceptable :


mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
        -> 1998-02-28

Notez que dans l'exemple précédent, le mot INTERVAL et le type type ne sont pas sensible à la casse.

EXTRACT(type FROM date)

La fonction EXTRACT() utilise les mêmes types d'intervalles que la fonction DATE_ADD() ou la fonction DATE_SUB() , mais extrait des parties de date plutôt que des opérations de date.


mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
       -> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
       -> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
       -> 20102

TO_DAYS(date)

Retourne le nombre de jours depuis la date 0 jusqu'à la date date :


mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('1997-10-07');
        -> 729669

TO_DAYS() n'est pas fait pour travailler avec des dates qui précèdent l'avènement du calendrier Grégorien (1582), car elle ne prend pas en compte les jours perdus lors du changement de calendrier.

FROM_DAYS(N)

Retourne la date correspondant au nombre de jours ( N ) depuis la date 0 :


mysql> SELECT FROM_DAYS(729669);
        -> '1997-10-07'

FROM_DAYS() n'est pas fait pour travailler avec des dates qui précèdent l'avènement du calendrier Grégorien (1582), car elle ne prend pas en compte les jours perdus lors du changement de calendrier.

DATE_FORMAT(date,format)

Formate la date date avec le format format . Les spécificateurs suivants peuvent être utilisé dans la chaîne format :

Specifier	Description
`%M`	Nom du mois ( `January` .. `December` )
`%W`	Nom du jour de la semaine ( `Sunday` .. `Saturday` )
`%D`	Jour du mois, avec un suffix anglais ( `1st` , `2nd` , `3rd` , etc.)
`%Y`	Année, au format numérique, sur 4 chiffres
`%y`	Année, au format numérique, sur 2 chiffres
`%X`	Année, pour les semaines qui commencent le Dimanche, au format numérique, sur 4 chiffres, utilisé avec '%V'
`%x`	Année, pour les semaines qui commencent le Lundi, au format numérique, sur 4 chiffres, utilisé avec '%v'
`%a`	Nom du jour de la semaine, en abrégé et en anglais ( `Sun` .. `Sat` )
`%d`	Jour du mois, au format numerique ( `00` .. `31` )
`%e`	Jour du mois, au format numerique ( `0` .. `31` )
`%m`	Mois, au format numerique ( `01` .. `12` )
`%c`	Mois, au format numérique ( `1` .. `12` )
`%b`	Nom du mois, en abrégé et en anglais ( `Jan` .. `Dec` )
`%j`	Jour de l'année ( `001` .. `366` )
`%H`	Heure ( `00` .. `23` )
`%k`	Heure ( `0` .. `23` )
`%h`	Heure ( `01` .. `12` )
`%I`	Heure ( `01` .. `12` )
`%l`	Heure ( `1` .. `12` )
`%i`	Minutes, au format numerique ( `00` .. `59` )
`%r`	Heures, au format 12-heures ( `hh:mm:ss [AP]M` )
`%T`	Heures, au format 24-heures ( `hh:mm:ss` )
`%S`	Secondes ( `00` .. `59` )
`%s`	Secondes ( `00` .. `59` )
`%p`	`AM` ou `PM`
`%w`	Numéro du jour de la semaine ( `0` =Sunday.. `6` =Saturday)
`%U`	Numéro de la semaine ( `00` .. `53` ), où Dimanche est le premier jour de la semaine
`%u`	Numéro de la semaine ( `00` .. `53` ), où Lundi est le premier jour de la semaine
`%V`	Numéro de la semaine ( `01` .. `53` ), où Dimanche est le premier jour de la semaine, utilisé avec '%X'
`%v`	Numéro de la semaine ( `01` .. `53` ), où Lundi est le premier jour de la semaine, utilisé avec '%x'
`%%`	Un signe pourcentage littéral `'%'` .

Tous les autres caractères sont simplement copiés dans le résultat sans interprétation:


mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'

A partir de MySQL version 3.23, le caractère '%' est nécessaire avant tous les caractères de spécification de format. Dans les anciennes versions, il était optionnel.

TIME_FORMAT(time,format): Cette fonction est utilisée exactement comme la fonction DATE_FORMAT() ci-dessus, mais la chaîne format ne doit utiliser que des spécificateurs d'heures, qui gèrent les heures, minutes et secondes. Les autres spécificateurs génèreront la valeur NULL ou 0 .

CURDATE()

CURRENT_DATE

Retourne la date courante au format 'YYYY-MM-DD' ou YYYYMMDD , suivant le contexte numérique ou chaîne :


mysql> SELECT CURDATE();
        -> '1997-12-15'
mysql> SELECT CURDATE() + 0;
        -> 19971215

CURTIME()

CURRENT_TIME

Retourne l'heure courante au format 'HH:MM:SS' ou HHMMSS , suivant le contexte numérique ou chaîne :


mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026

NOW()

SYSDATE()

CURRENT_TIMESTAMP

Retourne la date courante au format 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS , suivant le contexte numérique ou chaîne :


mysql> SELECT NOW();
        -> '1997-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 19971215235026

Notez que NOW() est évalué une seule fois par requête, au début de l'exécution de celle-ci. Cela signifie que si il y a de multiples références à NOW() dans une requête, la même date sera utilisé.

UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

Lorsqu'elle est appelé sans argument, cette fonction retourne un timestamp Unix (nombre de secondes depuis '1970-01-01 00:00:00' GMT). Si UNIX_TIMESTAMP() est appelé avec un argument date , elle retourne le timestamp correspondant à cette date. date peut être une chaîne de type DATE , DATETIME , TIMESTAMP , ou un nombre au format YYMMDD ou YYYYMMDD , en horaire local :


mysql> SELECT UNIX_TIMESTAMP();
        -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580

Lorsque UNIX_TIMESTAMP est utilisé sur une colonne de type TIMESTAMP , la fonction reçoit directement la valeur, sans conversion explicite. Si vous donnez à UNIX_TIMESTAMP() une date hors de son intervalle de validité, elle retourne 0.Si vous voulez soustraire une colonne de type UNIX_TIMESTAMP() , vous devez sûrement vouloir un résultat de type entier signé. Fonctions de transtypage .

FROM_UNIXTIME(unix_timestamp)

Retourne une représentation de l'argument unix_timestamp sous la forme 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS , suivant si la fonction est utilisé dans un contexte numérique ou de chaîne.


mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)

Retourne une chaîne représentant le timestamp Unix, formaté en accord avec la chaîne format . format doit contenir les mêmes spécificateurs que ceux utilisés par la fonction DATE_FORMAT() :


mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
                            '%Y %D %M %h:%i:%s %x');
        -> '1997 23rd December 03:43:30 1997'

SEC_TO_TIME(seconds)

Retourne l'argument seconds , convertit en heures, minutes et secondes au format 'HH:MM:SS' ou HHMMSS , suivant le contexte numérique ou chaîne :


mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938

TIME_TO_SEC(time)

Retourne l'argument time , convertit en secondes :


mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

<<	Fonctions de dates et d'heures	>>
Fonctions numériques	Fonctions à utiliser dans les clauses SELECT et WHERE	Fonctions de transtypage