測試環境為 CentOS 8 x86_64 虛擬機.
- 字串函數 ( String Functions ) 與 型別轉換 – https://benjr.tw/102953
- 數字函數 (Numeric Functions) – https://benjr.tw/101970
- 日期與時間函數 (Date & Time Functions) – 這邊介紹.
- 資訊函數 (Information Functions) – https://benjr.tw/102961
- 搭配 GROUP BY 函數 (Aggregate Functions) – https://benjr.tw/102967
- 流程控制函數 (Control Flow Functions) – https://benjr.tw/102747
先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).
SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 .
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, 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 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ; Query OK, 0 rows affected (0.010 sec) MariaDB [testdb]> DESCRIBE employee; +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | K1 | int(11) | NO | PRI | NULL | auto_increment | | Name | char(20) | NO | | NULL | | | Dept | char(20) | NO | | NULL | | | JobTitle | char(20) | NO | | NULL | | | Salary | int(11) | NO | | NULL | | | email | char(30) | NO | | NULL | | +----------+----------+------+-----+---------+----------------+ 6 rows in set (0.004 sec)
透過 INSERT 新增加了五筆資料.
MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') , ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept | JobTitle | Salary | email |
+----+------+---------+----------+--------+---------------+
| 1 | Ben | Testing | Engineer | 45000 | ben@benjr.tw |
| 2 | Afa | Power | Engineer | 48000 | Afa@benjr.tw |
| 3 | Boss | Testing | Manager | 75000 | Boss@benjr.tw |
| 4 | JoJo | Testing | Engineer | 45000 | jojo@benjr.tw |
| 5 | Ian | PM | Engineer | 85000 | ian@benjr.tw |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)
- CURRENT_TIMESTAMP , NOW
CURRENT_TIMESTAMP 與 NOW 功能都一樣,回傳目前系統的日期時間.MariaDB [(none)]> SELECT CURRENT_TIMESTAMP(); +---------------------+ | CURRENT_TIMESTAMP() | +---------------------+ | 2019-09-06 17:16:18 | +---------------------+ 1 row in set (0.00 sec)
MariaDB [(none)]> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2019-09-06 17:16:24 | +---------------------+ 1 row in set (0.00 sec)
- CURDATE
回傳目前日期.MariaDB [(none)]> SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2019-09-06 | +------------+ 1 row in set (0.01 sec)
- CURTIME
回傳目前時間.MariaDB [(none)]> SELECT CURTIME(); +-----------+ | CURTIME() | +-----------+ | 17:20:46 | +-----------+ 1 row in set (0.00 sec)
- DATE
回傳日期時間中的日期.MariaDB [testdb]> SELECT DATE(CURRENT_TIMESTAMP()); +---------------------------+ | DATE(CURRENT_TIMESTAMP()) | +---------------------------+ | 2020-02-04 | +---------------------------+ 1 row in set (0.000 sec)
- TIME
回傳日期時間中的時間.MariaDB [testdb]> SELECT TIME(CURRENT_TIMESTAMP()); +---------------------------+ | TIME(CURRENT_TIMESTAMP()) | +---------------------------+ | 20:21:43 | +---------------------------+ 1 row in set (0.000 sec)
- DATE_SUB , DATE_ADD
將減去 DATE_SUB ,或是增加 DATE_ADD 指定的時間.INTERVAL 後面可以接的時間為 YEAR , QUARTER , MONTH , WEEK , DAY , HOUR , MINUTE , SECOND , MICROSECOND . 更多格式請參考 https://mariadb.com/kb/en/addtime/
其他 ADDTIME 函數也是類似的功能,更多關於時間函數請參考 – https://mariadb.com/kb/en/date-and-time-units/MariaDB [(none)]> SELECT DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR); -------------------------------------------------+ | DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) | +-------------------------------------------------+ | 2017-09-06 17:22:36 | +-------------------------------------------------+ 1 row in set (0.01 sec)
MariaDB [(none)]> SELECT DATE_ADD(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR); -------------------------------------------------+ | DATE_ADD(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) | +-------------------------------------------------+ | 2021-09-06 17:23:03 | +-------------------------------------------------+ 1 row in set (0.01 sec)
- UNIX_TIMESTAMP , FROM_UNIXTIME
預設回傳從 1970-01-0100:00:00 的秒數(無符號整數),但我們的時間是 UTC + 8:00 ,所以可以看到要 1970-01-01 08:00:00 才是從 0 開始.MariaDB [testdb]> SELECT UNIX_TIMESTAMP("1970-01-01 00:00:00"); +---------------------------------------+ | UNIX_TIMESTAMP("1970-01-01 00:00:00") | +---------------------------------------+ | NULL | +---------------------------------------+ 1 row in set (0.000 sec)MariaDB [testdb]> SELECT UNIX_TIMESTAMP("1970-01-01 08:00:00"); +---------------------------------------+ | UNIX_TIMESTAMP("1970-01-01 08:00:00") | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.000 sec)回傳從 1970-01-0100:00:00 到現在的秒數差.
MariaDB [testdb]> SELECT UNIX_TIMESTAMP(); +------------------+ | UNIX_TIMESTAMP() | +------------------+ | 1587031426 | +------------------+ 1 row in set (0.001 sec)
MariaDB [testdb]> SELECT UNIX_TIMESTAMP(NOW()); +-----------------------+ | UNIX_TIMESTAMP(NOW()) | +-----------------------+ | 1587031910 | +-----------------------+ 1 row in set (0.000 sec)
這函數什麼時候會用到,目前我只有在利用時間欄位做資料分隔時會用到,詳細用法請參考 – https://benjr.tw/102725 .
UNIX_TIMESTAMP 是把時間從 1970-01-0100:00:00 到指定時間的秒數,相反的函數是 FROM_UNIXTIME 可以把 1970-01-0100:00:00 到指定時間的秒數轉成時間格式.
MariaDB [(none)]> SELECT FROM_UNIXTIME( 1587031910, '%Y-%m-%d %H:%i:%s' ); +--------------------------------------------------+ | FROM_UNIXTIME( 1587031910, '%Y-%m-%d %H:%i:%s' ) | +--------------------------------------------------+ | 2020-04-16 18:11:50 | +--------------------------------------------------+ 1 row in set (0.000 sec)
參數 : 更多格式請參考 – https://mariadb.com/kb/en/date_format/
- %Y – Year with 4 digits.
- %m – Month with 2 digits.
- %d – Day with 2 digits.
- %H – Hour with 2 digits between 00-23.
- %i – Minute with 2 digits.
- %s – Seconds with 2 digits.
- DATE_FORMAT
依據指定格式來顯示日期.MariaDB [(none)]> SELECT DATE_FORMAT(CURDATE(), "%m-%d"); +---------------------------------+ | DATE_FORMAT(CURDATE(), "%m-%d") | +---------------------------------+ | 09-26 | +---------------------------------+ 1 row in set (0.001 sec)
更多格式請參考 – https://mariadb.com/kb/en/date_format/
- DATEDIFF
比較兩個日期相差天數.MariaDB [(none)]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); +----------------------------------------------+ | DATEDIFF('2007-12-31 23:59:59','2007-12-30') | +----------------------------------------------+ | 1 | +----------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT DATEDIFF('2007-12-31','2007-12-30'); +-------------------------------------+ | DATEDIFF('2007-12-31','2007-12-30') | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.000 sec) - TIMEDIFF
比較兩個時間相差 時:分:秒 .MariaDB [(none)]> SELECT TIMEDIFF('2007-12-31 23:59:59','2007-12-30 01:01:00'); +-------------------------------------------------------+ | TIMEDIFF('2007-12-31 23:59:59','2007-12-30 01:01:00') | +-------------------------------------------------------+ | 46:58:59 | +-------------------------------------------------------+ 1 row in set (0.000 sec) - SEC_TO_TIME()
將秒數轉換成時間格式 hh:mm:ssMariaDB [(none)]> SELECT SEC_TO_TIME(100); +------------------+ | SEC_TO_TIME(100) | +------------------+ | 00:01:40 | +------------------+ 1 row in set (0.000 sec)
- STR_TO_DATE()
mysql 標準 timestamp 格式為 yyyy-mm-dd hh:mm:ss (24 小時制),如果從程式讀取的格式不同就可以透過這個函數作轉換.STR_TO_DATE(string, format)
MariaDB [testdb]> SELECT STR_TO_DATE("2/25/2020 11:13:03 PM", "%m/%d/%Y %h:%i:%s %p") AS TimeStamp; +---------------------+ | TimeStamp | +---------------------+ | 2020-02-25 23:13:03 | +---------------------+ 1 row in set (0.000 sec)更多參數請參考 – https://mariadb.com/kb/en/str_to_date/
- %m : Month with 2 digits.
- %d : Day with 2 digits.
- %Y : Year with 4 digits.
- %h : Hour with 2 digits between 01-12.
- %i : Minute with 2 digits.
- %s : Seconds with 2 digits.
- %p : AM/PM according to current locale.
沒有解決問題,試試搜尋本站其他內容