MySQL基礎知識04資料類型(四)日期時間的格式轉換

標籤：指定   mys   select   開始時間   rom   date   ros   bsp   cond   

1.  UNIX_TIMESTAMP

MySQL提供了UNIX_TIMESTAMP()函數，用於計算自從1970-01-01 08:00:00以來所經過的秒數。此處開始時間是1970-01-01 08:00:00，而不是1970-01-01 00:00:00。對於早於1970-01-01 08:00:00的時間，傳回值都為0。這個相對時間單位為秒，支援小數。

 

mysql> select unix_timestamp();

+------------------+

| unix_timestamp() |

+------------------+

|       1504246140 |

+------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp(‘1970-01-01 00:00:00‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-01 00:00:00‘) |

+---------------------------------------+

|                                     0 |

+---------------------------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp(‘1970-01-01 01:00:00‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-01 01:00:00‘) |

+---------------------------------------+

|                                     0 |

+---------------------------------------+

1 row in set (0.00 sec)

 

 

mysql> select unix_timestamp(‘1970-01-01 08:00:00‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-01 08:00:00‘) |

+---------------------------------------+

|                                     0 |

+---------------------------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp(‘1970-01-01 08:00:01‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-01 08:00:01‘) |

+---------------------------------------+

|                                     1 |

+---------------------------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp(‘1970-01-01 08:00:02‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-01 08:00:02‘) |

+---------------------------------------+

|                                     2 |

+---------------------------------------+

1 row in set (0.00 sec)

 

 

mysql> select unix_timestamp(‘1970-01-02 00:00:00‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-02 00:00:00‘) |

+---------------------------------------+

|                                 57600 |

+---------------------------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp(‘1970-01-03 00:00:00‘);

+---------------------------------------+

| unix_timestamp(‘1970-01-03 00:00:00‘) |

+---------------------------------------+

|                                144000 |

+---------------------------------------+

1 row in set (0.00 sec)

 

 

unix_timestamp()函數支援小數，但最多保留6位小數。

mysql> select unix_timestamp(‘1970-01-01 08:00:02.123456‘);

+----------------------------------------------+

| unix_timestamp(‘1970-01-01 08:00:02.123456‘) |

+----------------------------------------------+

|                                     2.123456 |

+----------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select unix_timestamp(‘1970-01-01 08:00:02.123456789‘);

+-------------------------------------------------+

| unix_timestamp(‘1970-01-01 08:00:02.123456789‘) |

+-------------------------------------------------+

|                                        2.123457 |

+-------------------------------------------------+

1 row in set (0.00 sec)

 

 

2.  FROM_UNIXTIME

 

FROM_UNIXTIME()函數將自1970-01-01 08:00:00以來的秒數，轉化為標準的字串時間戳記格式。

mysql> select from_unixtime( 0 );

+---------------------+

| from_unixtime( 0 )  |

+---------------------+

| 1970-01-01 08:00:00 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select from_unixtime( 1 );

+---------------------+

| from_unixtime( 1 )  |

+---------------------+

| 1970-01-01 08:00:01 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select from_unixtime( 1.123456 );

+----------------------------+

| from_unixtime( 1.123456 )  |

+----------------------------+

| 1970-01-01 08:00:01.123456 |

+----------------------------+

1 row in set (0.00 sec)

 

FROM_UNIXTIME()函數支援日期時間字串的格式化。

mysql> select from_unixtime( 1.123456 ,‘%Y-%m-%d %H:%i:%s‘);

+-----------------------------------------------+

| from_unixtime( 1.123456 ,‘%Y-%m-%d %H:%i:%s‘) |

+-----------------------------------------------+

| 1970-01-01 08:00:01                           |

+-----------------------------------------------+

1 row in set (0.00 sec)

 

 

3.  DATE_FORMAT

將日期和時間相互關聯類型轉化為指定格式的字串。

mysql> select date_format( now(),‘%Y-%m-%d %H:%i:%s‘);

+-----------------------------------------+

| date_format( now(),‘%Y-%m-%d %H:%i:%s‘) |

+-----------------------------------------+

| 2017-09-01 14:35:22                     |

+-----------------------------------------+

1 row in set (0.00 sec)

 

 

4.  SEC_TO_TIME

SEC_TO_TIME()函數將秒數轉化為時間。

 

mysql> select sec_to_time(0),sec_to_time(1),sec_to_time(3600),sec_to_time(36000),sec_to_time(360000);

+----------------+----------------+-------------------+--------------------+---------------------+

| sec_to_time(0) | sec_to_time(1) | sec_to_time(3600) | sec_to_time(36000) | sec_to_time(360000) |

+----------------+----------------+-------------------+--------------------+---------------------+

| 00:00:00       | 00:00:01       | 01:00:00          | 10:00:00           | 100:00:00           |

+----------------+----------------+-------------------+--------------------+---------------------+

1 row in set (0.00 sec)

 

 

5.  TIME_TO_SEC

TIME_TO_SEC()函數將時間轉化為秒數。

 

mysql> select time_to_sec(‘00:00:00‘), time_to_sec(‘00:00:01‘),time_to_sec(‘01:00:00‘),time_to_sec(‘100:00:00‘);

+-------------------------+-------------------------+-------------------------+--------------------------+

| time_to_sec(‘00:00:00‘) | time_to_sec(‘00:00:01‘) | time_to_sec(‘01:00:00‘) | time_to_sec(‘100:00:00‘) |

+-------------------------+-------------------------+-------------------------+--------------------------+

|                       0 |                       1 |                    3600 |                   360000 |

+-------------------------+-------------------------+-------------------------+--------------------------+

1 row in set (0.00 sec)

 

 

 

 

6.  TIMESTAMPDIFF

 

TIMESTAMPDIFF()函數用於計算兩個時間戳記之間的差異，單位可以指定為年月日時分秒中的任何一個，也可以指定為微秒。

mysql> select timestampdiff(YEAR,‘2017-09-01‘, ‘2000-09-01‘);

+------------------------------------------------+

| timestampdiff(YEAR,‘2017-09-01‘, ‘2000-09-01‘) |

+------------------------------------------------+

|                                            -17 |

+------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(YEAR,‘2017-09-01‘, ‘2027-09-01‘);

+------------------------------------------------+

| timestampdiff(YEAR,‘2017-09-01‘, ‘2027-09-01‘) |

+------------------------------------------------+

|                                             10 |

+------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(MONTH,‘2017-09-01‘, ‘2027-09-01‘);

+-------------------------------------------------+

| timestampdiff(MONTH,‘2017-09-01‘, ‘2027-09-01‘) |

+-------------------------------------------------+

|                                             120 |

+-------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(DAY,‘2017-09-01‘, ‘2017-10-01‘);

+-----------------------------------------------+

| timestampdiff(DAY,‘2017-09-01‘, ‘2017-10-01‘) |

+-----------------------------------------------+

|                                            30 |

+-----------------------------------------------+

1 row in set (0.00 sec)

 

 

mysql> select timestampdiff(HOUR,‘2017-09-01‘, ‘2017-10-01‘);

+------------------------------------------------+

| timestampdiff(HOUR,‘2017-09-01‘, ‘2017-10-01‘) |

+------------------------------------------------+

|                                            720 |

+------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(MINUTE,‘2017-09-01‘, ‘2017-10-01‘);

+--------------------------------------------------+

| timestampdiff(MINUTE,‘2017-09-01‘, ‘2017-10-01‘) |

+--------------------------------------------------+

|                                            43200 |

+--------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(SECOND,‘2017-09-01‘, ‘2017-10-01‘);

+--------------------------------------------------+

| timestampdiff(SECOND,‘2017-09-01‘, ‘2017-10-01‘) |

+--------------------------------------------------+

|                                          2592000 |

+--------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(MICROSECOND,‘2017-09-01‘, ‘2017-10-01‘);

+-------------------------------------------------------+

| timestampdiff(MICROSECOND,‘2017-09-01‘, ‘2017-10-01‘) |

+-------------------------------------------------------+

|                                         2592000000000 |

+-------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select timestampdiff(MICROSECOND,‘2017-09-01 00:00:00.123456‘, ‘2017-09-01 00:00:01.000001‘);

+---------------------------------------------------------------------------------------+

| timestampdiff(MICROSECOND,‘2017-09-01 00:00:00.123456‘, ‘2017-09-01 00:00:01.000001‘) |

+---------------------------------------------------------------------------------------+

|                                                                                876545 |

+---------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

MySQL基礎知識04資料類型(四)日期時間的格式轉換