SQL 資料型態 – TIMESTAMP

這邊來看一下 TIMESTAMP 這個資料型態.

測試環境為 Ubuntu16.04 x86_64 虛擬機.

建立一個有 timestamp 欄位的資料表 table ,預設值為 current_timestamp (日期時間函數,功能與 NOW 一樣,都是回傳目前系統的日期時間),on update CURRENT_TIMESTAMP 資料更新時,time stamp 也一同更新.

root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (
    -> Name VARCHAR(20),
    -> TS1 TIMESTAMP, 
    -> TS2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> TS3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.006 sec)

MariaDB [testdb]> DESCRIBE employee; 
+-------+-------------+------+-----+---------------------+-------------------------------+
| Field | Type        | Null | Key | Default             | Extra                         |
+-------+-------------+------+-----+---------------------+-------------------------------+
| Name  | varchar(20) | YES  |     | NULL                |                               |
| TS1   | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
| TS2   | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
| TS3   | timestamp   | NO   |     | current_timestamp() |                               |
+-------+-------------+------+-----+---------------------+-------------------------------+
4 rows in set (0.002 sec)

除了 Name 是 VARCHAR 外,其他欄位都是 TIMESTAMP ,其中

1. TS1 使用預設的 TIMESTAMP (預設沒有值時,會自動填上現在系統時間,並且更新資料時一併變更時間).
2. TS2 設定為會自動填上現在系統時間,並且更新資料時一併變更時間(同預設值)
3. TS3 會自動填上現在系統時間,但更新資料時不變更時間

建立一筆新資料時,time stamp 會自動產生,值為目前日期+時間.

MariaDB [testdb]> INSERT INTO employee (Name) VALUES ('Ben');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> SELECT * FROM employee;
+------+---------------------+---------------------+---------------------+
| Name | TS1                 | TS2                 | TS3                 |
+------+---------------------+---------------------+---------------------+
| Ben  | 2020-05-11 20:49:45 | 2020-05-11 20:49:45 | 2020-05-11 20:49:45 |
+------+---------------------+---------------------+---------------------+
1 row in set (0.000 sec)

資料更新時,TS1 , TS2 也會更新.

MariaDB [testdb]> UPDATE employee SET Name="Ben10" WHERE Name="Ben";
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+-------+---------------------+---------------------+---------------------+
| Name  | TS1                 | TS2                 | TS3                 |
+-------+---------------------+---------------------+---------------------+
| Ben10 | 2020-05-11 20:50:30 | 2020-05-11 20:50:30 | 2020-05-11 20:49:45 |
+-------+---------------------+---------------------+---------------------+
1 row in set (0.001 sec)

update CURRENT_TIMESTAMP

如果不希望更新時把 Timestamp 也同步更新,可以使用 ALTER 把 on update CURRENT_TIMESTAMP 移除.

MariaDB [testdb]> ALTER TABLE employee CHANGE COLUMN TS1 TS1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> DESCRIBE employee; 
+-------+-------------+------+-----+---------------------+-------------------------------+
| Field | Type        | Null | Key | Default             | Extra                         |
+-------+-------------+------+-----+---------------------+-------------------------------+
| Name  | varchar(20) | YES  |     | NULL                |                               |
| TS1   | timestamp   | NO   |     | current_timestamp() |                               |
| TS2   | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
| TS3   | timestamp   | NO   |     | current_timestamp() |                               |
+-------+-------------+------+-----+---------------------+-------------------------------+
4 rows in set (0.002 sec)

恢復一樣用 ALTER 修改.

MariaDB [testdb]> ALTER TABLE employee CHANGE COLUMN TS1 TS1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> DESCRIBE employee; 
+-------+-------------+------+-----+---------------------+-------------------------------+
| Field | Type        | Null | Key | Default             | Extra                         |
+-------+-------------+------+-----+---------------------+-------------------------------+
| Name  | varchar(20) | YES  |     | NULL                |                               |
| TS1   | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
| TS2   | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
| TS3   | timestamp   | NO   |     | current_timestamp() |                               |
+-------+-------------+------+-----+---------------------+-------------------------------+
4 rows in set (0.001 sec)

日期與時間資料型態

其他與日期與時間相關的資料型態

Data Type	“Zero” Value
DATE	‘0000-00-00’
TIME	’00:00:00′
DATETIME	‘0000-00-00 00:00:00’
TIMESTAMP	‘0000-00-00 00:00:00’
YEAR	0000

時間的比較

先新增一筆資料.

MariaDB [testdb]> INSERT INTO employee (Name,TS1) VALUES ('Ben',DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR));
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> SELECT * FROM employee;
+-------+---------------------+---------------------+---------------------+
| Name  | TS1                 | TS2                 | TS3                 |
+-------+---------------------+---------------------+---------------------+
| Ben10 | 2020-05-11 20:50:30 | 2020-05-11 20:50:30 | 2020-05-11 20:49:45 |
| Ben   | 2018-05-11 20:54:50 | 2020-05-11 20:54:50 | 2020-05-11 20:54:50 |
+-------+---------------------+---------------------+---------------------+
2 rows in set (0.001 sec)

函數說明:

• CURRENT_TIMESTAMP()
  功能與 NOW 一樣,都是回傳目前系統的日期時間.
• DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR)
  將剪去指定的時間.INTERVAL 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.

搜尋工作超過一年的員工.

MariaDB [testdb]> SELECT * FROM employee WHERE TS1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR);
+------+---------------------+---------------------+---------------------+
| Name | TS1                 | TS2                 | TS3                 |
+------+---------------------+---------------------+---------------------+
| Ben  | 2018-05-11 20:54:50 | 2020-05-11 20:54:50 | 2020-05-11 20:54:50 |
+------+---------------------+---------------------+---------------------+
1 row in set (0.001 sec)

其他與日期與時間相關的函式:
CURDATE() – 回傳目前日期.
CURTIME() – 回傳目前時間.

更多關於 日期與時間的函數請參考 https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html