這邊來看一下 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 ,其中
- TS1 使用預設的 TIMESTAMP (預設沒有值時,會自動填上現在系統時間,並且更新資料時一併變更時間).
- TS2 設定為會自動填上現在系統時間,並且更新資料時一併變更時間(同預設值)
- 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
沒有解決問題,試試搜尋本站其他內容