DateTime Example in MySQL

要点

• 数据类型： datetime
• 设置日期： date()函数，此时，时间部分为0。如 date(‘2016-05-23 20:24:00’)
• 设置时间： time()函数，此时日期为当天的日期。如 time(‘2016-05-23 20:24:00’)
• 设置日期&时间： timestamp()函数，如 timestamp(‘2016-05-23 20:24:00’)

Prepare the Test Database

MariaDB [(none)]> CREATE DATABASE testdate;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> USE testdate;
Database changed
MariaDB [testdate]

Create a Table

MariaDB [testdate]> CREATE TABLE ttt (others VARCHAR(20), the_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.25 sec)

MariaDB [testdate]> DESCRIBE ttt;
+----------+-------------+------+-----+-------------------+-------+
| Field    | Type        | Null | Key | Default           | Extra |
+----------+-------------+------+-----+-------------------+-------+
| others   | varchar(20) | YES  |     | NULL              |       |
| the_datetime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+----------+-------------+------+-----+-------------------+-------+
2 rows in set (0.02 sec)

MariaDB [testdate]>

Insert Some Records

Insert with DATE()

MariaDB [testdate]> INSERT INTO ttt VALUES ("a", DATE("2016-05-24"));
Query OK, 1 row affected (0.05 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime            |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
+--------+---------------------+
1 row in set (0.00 sec)
MariaDB [testdate]> 

The hour, minute, and second was set to default value, ie. 00:00:00.

Insert with Time()

MariaDB [testdate]> INSERT INTO ttt VALUES ("b", TIME("14:31:01"));
Query OK, 1 row affected (0.04 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime            |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
+--------+---------------------+
2 rows in set (0.00 sec)

MariaDB [testdate]> 

If Time() was used, only the time portion is valid, and the date was set to default, ie. current date.

Date() with time portion

MariaDB [testdate]> INSERT INTO ttt VALUES ("a", DATE("2016-05-24 14:33:20"));
Query OK, 1 row affected (0.04 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime            |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
| a      | 2016-05-24 00:00:00 |
+--------+---------------------+
3 rows in set (0.00 sec)

MariaDB [testdate]>

In this exmple, Date() was used, and the time portion was ignored, — set to default time 00:00:00.

Time() with date portion

MariaDB [testdate]> INSERT INTO ttt VALUES ("a", TIME("2016-05-25 14:33:20"));
Query OK, 1 row affected (0.05 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime            |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
| a      | 2016-05-24 00:00:00 |
| a      | 2016-05-24 14:33:20 |
+--------+---------------------+
4 rows in set (0.00 sec)

MariaDB [testdate]>

TimeStamp()

MariaDB [testdate]> INSERT INTO ttt VALUES ("a", TIMESTAMP("2016-05-24 14:33:30"));
Query OK, 1 row affected (0.06 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime            |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
| a      | 2016-05-24 00:00:00 |
| a      | 2016-05-24 14:33:20 |
| a      | 2016-05-24 14:33:30 |
+--------+---------------------+
5 rows in set (0.00 sec)

MariaDB [testdate]>

The TimeStamp() is used to create datetime.

Query with datetime comparation

MariaDB [testdate]> DESCRIBE ttt;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| others       | varchar(20) | YES  |     | NULL              |       |
| the_datetime | datetime    | NO   |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime        |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
| a      | 2016-05-24 00:00:00 |
| a      | 2016-05-24 14:33:20 |
| a      | 2016-05-24 14:33:30 |
+--------+---------------------+
5 rows in set (0.00 sec)

MariaDB [testdate]> SELECT * FROM ttt WHERE the_datetime < TIMESTAMP('2016-5-24 14:32:00');
+--------+---------------------+
| others | the_datetime        |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
| a      | 2016-05-24 00:00:00 |
+--------+---------------------+
3 rows in set (0.00 sec)

MariaDB [testdate]> SELECT * FROM ttt WHERE the_datetime > TIMESTAMP('2016-5-24 14:32:00');
+--------+---------------------+
| others | the_datetime        |
+--------+---------------------+
| a      | 2016-05-24 14:33:20 |
| a      | 2016-05-24 14:33:30 |
+--------+---------------------+
2 rows in set (0.00 sec)

MariaDB [testdate]>

Delete Some Records

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime        |
+--------+---------------------+
| a      | 2016-05-24 00:00:00 |
| b      | 2016-05-24 14:31:01 |
| a      | 2016-05-24 00:00:00 |
| a      | 2016-05-24 14:33:20 |
| a      | 2016-05-24 14:33:30 |
+--------+---------------------+
5 rows in set (0.00 sec)

MariaDB [testdate]> DELETE FROM ttt WHERE the_datetime < TIMESTAMP('2016-5-24 14:32:00');
Query OK, 3 rows affected (0.03 sec)

MariaDB [testdate]> SELECT * FROM ttt;
+--------+---------------------+
| others | the_datetime        |
+--------+---------------------+
| a      | 2016-05-24 14:33:20 |
| a      | 2016-05-24 14:33:30 |
+--------+---------------------+
2 rows in set (0.00 sec)

MariaDB [testdate]>