Mega Code Archive
Dates and times in SQL commands must be given as character strings
<![CDATA[
mysql>
mysql>
The following three queries are equivalent, and work for both DATETIME and TIMESTAMP columns:
mysql>
mysql> CREATE TABLE mytable
-> (
-> ts TIMESTAMP NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 10 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 9 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 8 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 7 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 6 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 5 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 4 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 3 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 2 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 1 DAY);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mytable(ts) VALUES (NOW() - INTERVAL 0 DAY);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT COUNT(*) FROM mytable WHERE ts BETWEEN '2004-05-16 08:34:07' AND '2005-02-11 00:15:44';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT COUNT(*) FROM mytable WHERE ts BETWEEN '2004/05/16 08:34:07' AND '2005/02/11 00:15:44';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT COUNT(*) FROM mytable WHERE ts BETWEEN '20110516083407' AND '20110211001544';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>