Detailed explanation of MySQL date string timestamp conversion

The conversion between time, string and timestamp is commonly used in daily life. Although it is commonly used, I like to search for the usage almost every time I use it. This article will serve as a note to organize the conversion between the three (i.e.: date to string, date to timestamp, string to date, string to timestamp, timestamp to date, timestamp to string) for easy reference in the future;

Functions involved

date_format(date, format) function, MySQL date formatting function date_format()

unix_timestamp() Function

str_to_date(str, format) function

from_unixtime(unix_timestamp, format) function, MySQL timestamp formatting function from_unixtime

Time to string

select date_format(now(), '%Y-%m-%d'); 
#Result: 2016-01-05

Time to timestamp

select unix_timestamp(now());  
# Result: 1452001082

String to time

select str_to_date('2016-01-02', '%Y-%m-%d %H'); 
#Result: 2016-01-02 00:00:00

Convert string to timestamp

select unix_timestamp('2016-01-02');  
# Result: 1451664000

Timestamp to time

select from_unixtime(1451997924); 
#Result: 2016-01-05 20:45:24

Timestamp to string

select from_unixtime(1451997924,'%Y-%d');  
//Result: 2016-01-05 20:45:24

Schedule

MySQL date format value range.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.