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)

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