MariaDB – 常用處理日期與時間函數 ( Date & Time Functions )

測試環境為 CentOS 8 x86_64 虛擬機.

  1. 字串函數 ( String Functions )http://benjr.tw/102953
  2. 數字函數 (Numeric Functions)http://benjr.tw/101970
  3. 日期與時間函數 (Date & Time Functions) – 這邊介紹.
  4. 資訊函數 (Information Functions)http://benjr.tw/102961
  5. 搭配 GROUP BY 函數 (Aggregate Functions)http://benjr.tw/102967
  6. 流程控制函數 (Control Flow Functions)http://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 ,請參考資料庫編碼 – http://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 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.

    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
    預設回傳從 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)
    

    這函數什麼時候會用到,目前我只有在利用時間欄位做資料分隔時會用到,詳細用法請參考 – http://benjr.tw/102725 .

  • 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/

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料