MariaDB fonctions de date et de temps
Par PlaceOweb le lundi, décembre 28 2015, 21:21 - SQL - Lien permanent
Comment gérer ses dates avec MariaDB, soustraire des dates, ajouter des intervalles de temps, ...
... extraire les secondes, minutes, jours, semaines, mois, ans...
Au même titre que MySQL fonctions de date et de temps, voici comment gérer les dates et times avec MariaDB, selon Date and Time Functions et Date and Time Data Types
MariaDB> SELECT VERSION(), CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, UNIX_TIMESTAMP(); +-------------------+---------------------+--------------+--------------+------------------+ | VERSION() | CURRENT_TIMESTAMP | CURRENT_DATE | CURRENT_TIME | UNIX_TIMESTAMP() | +-------------------+---------------------+--------------+--------------+------------------+ | 10.0.16-MariaDB-1 | 2011-12-29 09:37:53 | 2011-12-29 | 09:37:53 | 1325147873| +-------------------+---------------------+--------------+--------------+------------------+
Soustraire 2 dates times, différence de la soustraction de 2 DATETIME
MariaDB> 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)
MariaDB> 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' |
+---------+------+---------------------------------------------------------+
-- Soustraction correcte de date, mais qui retourne une période et non pas des secondes
MariaDB> SELECT CAST('2011-10-26 08:01:20' AS DATETIME) - CAST('2011-10-26 08:00:00' AS DATETIME) AS maDate;
+--------+
| maDate |
+--------+
| 120 |
+--------+
MariaDB> SELECT CAST('2011-10-27 08:01:20' AS DATETIME) - CAST('2011-10-26 08:00:00' AS DATETIME) AS maDate;
+---------+
| maDate |
+---------+
| 1000120 |
+---------+
-- BAD : Soustraction INcorrecte de date
MariaDB> 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 |
+--------+
-- BAD : Soustraction INcorrecte de date
MariaDB> 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 |
+--------+
-- Soustraction correcte de date en secondes unix
MariaDB> SELECT UNIX_TIMESTAMP('2011-10-26 08:01:20') - UNIX_TIMESTAMP('2011-10-26 08:00:00') AS maDate;
+--------+
| maDate |
+--------+
| 80 |
+--------+
MariaDB> SELECT UNIX_TIMESTAMP('2011-10-27 08:01:20') - UNIX_TIMESTAMP('2011-10-26 08:00:00') AS maDate;
+--------+
| maDate |
+--------+
| 86480 |
+--------+
-- OK : 3600s * 24h = 86400s + 1min (60s) + 20s = 86480s
MariaDB> SELECT DATEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
+--------------------------------------------------------+
| DATEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+--------------------------------------------------------+
| 1 |
+--------------------------------------------------------+
MariaDB> SELECT PERIOD_DIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are not date values.
+-----------------------------------------------------------+
| PERIOD_DIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
SELECT TIMEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- TIMEDIFF() returns expr1 - expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
+--------------------------------------------------------+
| TIMEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+--------------------------------------------------------+
| 24:01:20 |
+--------------------------------------------------------+
-- DATEDIFF - Difference in days between two date/time values
MariaDB> SELECT DATEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
+--------------------------------------------------------+
| DATEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+--------------------------------------------------------+
| 1 |
+--------------------------------------------------------+
-- 1 jour d''écart, le temps est ignorée.
-- PERIOD_DIFF - Number of months between two periods
MariaDB> SELECT PERIOD_DIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are not date values.
+-----------------------------------------------------------+
| PERIOD_DIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
-- TIMEDIFF - Returns the difference between two date/times
SELECT TIMEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- TIMEDIFF() returns expr1 - expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
+--------------------------------------------------------+
| TIMEDIFF('2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+--------------------------------------------------------+
| 24:01:20 |
+--------------------------------------------------------+
-- 24heures 01minute 20secondes
-- TIMESTAMPDIFF - Difference between two datetimes
MariaDB> SELECT TIMESTAMPDIFF(SECOND,'2011-10-27 08:01:20', '2011-10-26 08:00:00'); -- Returns datetime_expr2 - datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
+--------------------------------------------------------------------+
| TIMESTAMPDIFF(SECOND,'2011-10-27 08:01:20', '2011-10-26 08:00:00') |
+--------------------------------------------------------------------+
| -86480 |
+--------------------------------------------------------------------+
-- 86480s (24heures 01minute 20secondes)
La gestion des mois de dates en anglais sous MariaDB
-- %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)
MariaDB> 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 |
+------------+----------------------+-----------------------+
MariaDB> 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 |
+--------------------------------------+-----------------------------+-----------------------------------------------+-------------------------------+
1 row in set, 1 warning (0.00 sec)
-- 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
MariaDB> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: 'badInputDateString' for function str_to_date |
+---------+------+-------------------------------------------------------------------------+