這幾天寫代碼,反複用到将日期時間字段轉化成數字串和日期格式字元串的情況,感覺自己還處于壹知半解之中,索性花點時間查下相關資料,基本弄明白了 MySQL 中兩個常用的日期函數的用法,記錄于此,作為筆記供參考。
1、FROM_UNIXTIME( unix_timestamp )
參數:通常是壹個十位的數字,如:1344887103
傳回值:有兩種,可能是類似 'YYYY-MM-DD HH:MM:SS' 這樣的字元串,也有可能是類似于 YYYYMMDDHHMMSS.uuuuuu 這樣的數字,具體傳回什麼取決于該函數被調用的形式。
1 | mysql> select FROM_UNIXTIME(1344887103); |
2 | + ---------------------------+ |
3 | | FROM_UNIXTIME(1344887103) | |
4 | + ---------------------------+ |
5 | | 2012-08-14 03:45:03 | |
6 | + ---------------------------+ |
7 | 1 row in set (0.00 sec) |
2、FROM_UNIXTIME( unix_timestamp ,format )
參數 unix_timestamp :與方法 FROM_UNIXTIME( unix_timestamp ) 中的參數含義一樣;
參數 format : 轉換之後的時間字元串顯示的格式;
傳回值:按照指定的時間格式顯示的字元串;
01 | mysql> select FROM_UNIXTIME(1344887103, '%Y-%M-%D %h:%i:%s' ); |
02 | + -----------------------------------------------+ |
03 | | FROM_UNIXTIME(1344887103, '%Y-%M-%D %h:%i:%s' ) | |
04 | + -----------------------------------------------+ |
05 | | 2012-August-14th 03:45:03 | |
06 | + -----------------------------------------------+ |
07 | 1 row in set (0.00 sec) |
08 | mysql> select FROM_UNIXTIME(1344887103, '%Y-%m-%D %h:%i:%s' ); |
09 | + -----------------------------------------------+ |
10 | | FROM_UNIXTIME(1344887103, '%Y-%m-%D %h:%i:%s' ) | |
11 | + -----------------------------------------------+ |
12 | | 2012-08-14th 03:45:03 | |
13 | + -----------------------------------------------+ |
15 | 1 row in set (0.00 sec) |
參考連結:https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime
1、UNIX_TIMESTAMP()
1 | mysql> select unix_timestamp(); |
7 | 1 row in set (0.00 sec) |
01 | mysql> SELECT UNIX_TIMESTAMP(); |
07 | 1 row in set (0.00 sec) |
09 | mysql> SELECT UNIX_TIMESTAMP( '2012-08-14 16:19:23' ); |
10 | + ---------------------------------------+ |
11 | | UNIX_TIMESTAMP( '2012-08-14 16:19:23' ) | |
12 | + ---------------------------------------+ |
14 | + ---------------------------------------+ |
15 | 1 row in set (0.00 sec) |
01 | mysql> SELECT UNIX_TIMESTAMP( '2005-03-27 03:00:00' ); |
02 | + ---------------------------------------+ |
03 | | UNIX_TIMESTAMP( '2005-03-27 03:00:00' ) | |
04 | + ---------------------------------------+ |
06 | + ---------------------------------------+ |
07 | mysql> SELECT UNIX_TIMESTAMP( '2005-03-27 02:00:00' ); |
08 | + ---------------------------------------+ |
09 | | UNIX_TIMESTAMP( '2005-03-27 02:00:00' ) | |
10 | + ---------------------------------------+ |
12 | + ---------------------------------------+ |
13 | mysql> SELECT FROM_UNIXTIME(1111885200); |
14 | + ---------------------------+ |
15 | | FROM_UNIXTIME(1111885200) | |
16 | + ---------------------------+ |
17 | | 2005-03-27 03:00:00 | |
18 | + ---------------------------+ |