MySQL fonctions de date et de temps
Par PlaceOweb le mercredi, octobre 26 2011, 20:31 - SQL - Lien permanent
Comment gérer ses dates avec MySQL, soustraire des dates, ajouter des intervalles de temps, ...
... extraire les secondes, minutes, jours, semaines, mois, ans...
Les entrées récentes selon un intervalle d'un mois
/** * Sélection des entrée de ma table qui ont une date récente (moins d'un mois en rapport à la date heure courante) */ SELECT * FROM maTable WHERE CURRENT_TIMESTAMP - INTERVAL 1 MONTH > maColonneDateTimeOuTimeStamp
Soustraire 2 dates times en secondes
/** * La date heure en cours */ mysql>SELECT CURRENT_TIMESTAMP; +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2011-10-26 12:12:16 | +---------------------+ 1 row in set (0.00 sec) /** * Mauvaise soustraction de date */ mysql>SELECT '2011-10-26 08:01:20' - '2011-10-26 08:00:00' AS maDate; +--------+ | maDate | +--------+ | 0 | +--------+ 1 row in set, 2 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-10-26 08:01:20' | | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-10-26 08:00:00' | +---------+------+---------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>SELECT CURRENT_TIMESTAMP - '2011-10-26 08:00:00' AS maDate; +----------------+ | maDate | +----------------+ | 20111026119112 | +----------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-10-26 08:00:00' | +---------+------+---------------------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT CURRENT_TIMESTAMP - CAST('2011-10-26 08:00:00' AS DATETIME) AS maDate; +--------------+ | maDate | +--------------+ | 41134.000000 | +--------------+ 1 row in set (0.01 sec) /** * Soustraction correcte de date, mais qui retourne une période et non pas des secondes */ mysql>SELECT CAST('2011-10-26 08:01:20' AS DATETIME) - CAST('2011-10-26 08:00:00' AS DATETIME) AS maDate; +------------+ | maDate | +------------+ | 120.000000 | +------------+ 1 row in set (0.00 sec) mysql>SELECT CAST('2011-10-27 08:01:20' AS DATETIME) - CAST('2011-10-26 08:00:00' AS DATETIME) AS maDate; +----------------+ | maDate | +----------------+ | 1000120.000000 | +----------------+ 1 row in set (0.00 sec) mysql>SELECT TIME_TO_SEC(CAST('2011-10-26 08:01:20' AS DATETIME) - CAST('2011-10-26 08:00:00' AS DATETIME)) AS maDate; +--------+ | maDate | +--------+ | 80 | +--------+ 1 row in set (0.00 sec) mysql>SELECT TIME_TO_SEC(CAST('2011-10-27 08:01:20' AS DATETIME) - CAST('2011-10-26 08:00:00' AS DATETIME)) AS maDate; +--------+ | maDate | +--------+ | 360080 | +--------+ 1 row in set (0.00 sec) /** * Soustraction correcte de date en secondes unix */ mysql>SELECT UNIX_TIMESTAMP('2011-10-26 08:01:20') - UNIX_TIMESTAMP('2011-10-26 08:00:00') AS maDate; +--------+ | maDate | +--------+ | 80 | +--------+ 1 row in set (0.00 sec) mysql>SELECT UNIX_TIMESTAMP('2011-10-27 08:01:20') - UNIX_TIMESTAMP('2011-10-26 08:00:00') AS maDate; +--------+ | maDate | +--------+ | 86480 | +--------+ 1 row in set (0.00 sec) mysql>SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP('2011-10-26 08:00:00') AS maDate; +--------+ | maDate | +--------+ | 15801 | +--------+ 1 row in set (0.00 sec)
- MySQL date and time functions - page 1 (2003) DATE_ADD INTERVAL PERIOD_ADD PERIOD_DIFF
- MySQL date and time functions - page 2 DATE_FORMAT UNIX_TIMESTAMP
- MySQL date and time functions - page 2 CURRENT_DATE CURRENT_TIME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_UNIXTIME MINUTE HOUR MONTH WEEK YEAR NOW QUARTER SEC_TO_TIME TIME_FORMAT TIME_TO_SEC TO_DAYS
La gestion des mois de dates en anglais sous MySQL
-- %a Nom du jour de la semaine, en abrégé et en anglais (Sun..Sat) -- %b Nom du mois, en abrégé et en anglais (Jan..Dec) -- %M Nom du mois (January..December) mysql> SELECT @d, DATE_FORMAT(@d,'%b'), DATE_FORMAT(@d, '%M'); +------------+----------------------+-----------------------+ | @d | DATE_FORMAT(@d,'%b') | DATE_FORMAT(@d, '%M') | +------------+----------------------+-----------------------+ | 2011-01-01 | Jan | January | | 2011-02-01 | Feb | February | | 2011-03-01 | Mar | March | | 2011-04-01 | Apr | April | | 2011-05-01 | May | May | | 2011-06-01 | Jun | June | | 2011-07-01 | Jul | July | | 2011-08-01 | Aug | August | | 2011-09-01 | Sep | September | | 2011-10-01 | Oct | October | | 2011-11-01 | Nov | November | | 2011-12-01 | Dec | December | +------------+----------------------+-----------------------+ mysql> SELECT STR_TO_DATE('07-OCT-11', '%d-%b-%Y'), STR_TO_DATE('', '%d-%b-%Y'), STR_TO_DATE('badInputDateString', '%d-%b-%Y'), STR_TO_DATE(NULL, '%d-%b-%Y'); +--------------------------------------+-----------------------------+-----------------------------------------------+-------------------------------+ | STR_TO_DATE('07-OCT-11', '%d-%b-%Y') | STR_TO_DATE('', '%d-%b-%Y') | STR_TO_DATE('badInputDateString', '%d-%b-%Y') | STR_TO_DATE(NULL, '%d-%b-%Y') | +--------------------------------------+-----------------------------+-----------------------------------------------+-------------------------------+ | 2011-10-07 | 0000-00-00 | NULL | NULL | +--------------------------------------+-----------------------------+-----------------------------------------------+-------------------------------+ -- Avec une date NULL ou invalide, le retour est NULL -- avec une date vide on obtient la date vide par défaut (selon configuration du serveur) : 0000-00-00 -- avec une date valide la date est bien parsée et convertie en DATE valide