Detailed explanation of MySQL date string timestamp interconversion
- 2021-07-01 08:22:39
- OfStack
Usually more commonly used time, string, time stamp between the mutual conversion, although commonly used but almost every time to use time like to search 1 usage; This article will be used as a note to sort out the usage of the conversion between the following three persons (i.e., date to string, date to timestamp, string to date, string to timestamp, timestamp to date, timestamp to string), which is convenient for future viewing;
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');
# Results: 2016-01-05
Time to time stamp
select unix_timestamp(now());
# Results: 1452001082
String to time
select str_to_date('2016-01-02', '%Y-%m-%d %H');
# Results: 2016-01-02 00:00:00
String to timestamp
select unix_timestamp('2016-01-02');
# Results: 1451664000
Time stamp to time
select from_unixtime(1451997924);
# Results: 2016-01-05 20:45:24
Time stamp to string
select from_unixtime(1451997924,'%Y-%d');
// Results: 2016-01-05 20:45:24
Schedule
MySQL date formatting (format) value range.
值 | 含义 | |
---|---|---|
秒 | %S、%s | 两位数字形式的秒( 00,01, ..., 59) |
分 | %I、%i | 两位数字形式的分( 00,01, ..., 59) |
小时 | %H | 24小时制,两位数形式小时(00,01, ...,23) |
%h | 12小时制,两位数形式小时(00,01, ...,12) | |
%k | 24小时制,数形式小时(0,1, ...,23) | |
%l | 12小时制,数形式小时(0,1, ...,12) | |
%T | 24小时制,时间形式(HH:mm:ss) | |
%r | 12小时制,时间形式(hh:mm:ss AM 或 PM) | |
%p | AM上午或PM下午 | |
周 | %W | 1周中每1天的名称(Sunday,Monday, ...,Saturday) |
%a | 1周中每1天名称的缩写(Sun,Mon, ...,Sat) | |
%w | 以数字形式标识周(0=Sunday,1=Monday, ...,6=Saturday) | |
%U | 数字表示周数,星期天为周中第1天 | |
%u | 数字表示周数,星期1为周中第1天 | |
天 | %d | 两位数字表示月中天数(01,02, ...,31) |
%e | 数字表示月中天数(1,2, ...,31) | |
%D | 英文后缀表示月中天数(1st,2nd,3rd ...) | |
%j | 以3位数字表示年中天数(001,002, ...,366) | |
月 | %M | 英文月名(January,February, ...,December) |
%b | 英文缩写月名(Jan,Feb, ...,Dec) | |
%m | 两位数字表示月份(01,02, ...,12) | |
%c | 数字表示月份(1,2, ...,12) | |
年 | %Y | 4位数字表示的年份(2015,2016...) |
%y | 两位数字表示的年份(15,16...) | |
文字输出 | %文字 | 直接输出文字内容 |