MySQL Basics 04 data type (iv) format conversion for datetime

1. Unix_timestamp

MySQL provides the unix_timestamp () function to calculate The number of seconds that have elapsed since 1970-01-01 08:00:00. The start time here is 1970-01-01 08:00:00, not 1970-01-01 00:00:00. For time earlier than 1970-01-01 08:00:00 , the return value is 0. This relative time unit is seconds, which supports decimals.

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)

The Unix_timestamp () function supports decimals, but retains up to 6 decimal places.

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

The From_unixtime () function Converts the number of seconds since 1970-01-01 08:00:00 to the standard string timestamp format.

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)

The From_unixtime () function supports formatting of date-time strings.

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

Converts a date and time-related type to a string of the specified 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

The Sec_to_time () function converts the number of seconds to a 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

The Time_to_sec () function converts the time to seconds.

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

The Timestampdiff () function is used to calculate the difference between two timestamps, which can be specified as either one of the days of the month or the minute, or as a microsecond.

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 Basics 04 data type (iv) format conversion for datetime