MySQL中各種日期格式轉換

日期格式的轉換總結

1)、unix time和日期格式互轉

比如zabbix history表中的clock字段儲存的是unix time，看到的是一串數字，可以使用FROM_UNIXTIME去轉換

unixtime轉換為日期格式：

select FROM_UNIXTIME(unix time)      

日期格式反轉成unixtime格式：

select UNIX_TIMESTAMP(SUBDATE(now(),interval 5 minute))      

2)、日期和字元串之間的轉換

oracle中把日期轉換成自定義格式的字元串使用to_char,反之使用to_date，但是mysql中是不一樣的

str_to_date('2012-05-01 23:59:59','%Y-%m-%d %T')
date_to_str(date字段,'%Y-%m-%d %T')      

日期格式說明：

%Y：代表4位的年份
%y：代表2為的年份
%m：代表月, 格式為(01……12) 
%c：代表月, 格式為(1……12)
%d：代表月份中的天數,格式為(00……31) 
%e：代表月份中的天數, 格式為(0……31)
%H：代表小時,格式為(00……23) 
%k：代表 小時,格式為(0……23) 
%h： 代表小時,格式為(01……12) 
%I： 代表小時,格式為(01……12) 
%l ：代表小時,格式為(1……12)
%i： 代表分鐘, 格式為(00……59)
%r：代表 時間,格式為12 小時(hh:mm:ss [AP]M) 
%T：代表 時間,格式為24 小時(hh:mm:ss)
%S：代表 秒,格式為(00……59) 
%s：代表 秒,格式為(00……59)      

3)、表示N小時、N分鐘、N秒前後的時間

--取目前時間：

select current_timestamp;
輸出：2016-06-16 16:12:52
select now(); 
輸出：2016-06-16 16:12:52
目前時間格式化為小時
select DATE_FORMAT(now(), '%Y-%m-%d %H:00:00');      

--取目前時間的前一分鐘：

select SUBDATE(now(),interval 60 second);
輸出：2016-06-16 16:11:52      

--取目前時間的下一分鐘：

select ADDDATE(now(),interval 60 second);
輸出：2016-06-16 16:13:52
通過變化上面的機關。可以取前後  分鐘，小時，天的時間      

--取前一分鐘的時間：

select SUBDATE(now(),interval 1 minute);
輸出：2016-06-16 16:16:38      

--取前一小時的時間：

select SUBDATE(now(),interval 1 hour);
輸出：2016-06-16 15:17:38      

--取前一天的時間：

select SUBDATE(now(),interval 1 day)；
輸出：2016-06-15 16:17:38      

--取後一分鐘的時間：

select ADDDATE(now(),interval 1 minute);
輸出：2016-06-16 16:17:38      

--取後一小時的時間：

select ADDDATE(now(),interval 1 hour);
輸出：2016-06-16 17:17:38      

--取後一天的時間：

select ADDDATE(now(),interval 1 day);
輸出：2016-06-17 16:17:38      

4)、擷取當天,昨天,本周,本月,上周,上月的起始時間

-- 今天

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天開始';
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天結束';      

-- 昨天

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天開始';
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天結束';      

-- 上周

SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';      

-- 本周

--美國算法
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
-- 上面的本周算法在中國會有問題,因為mysql是按照周日為一周第一天,如果目前是周日的話,會把時間定為到下一周.
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';      

-- 上月

SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';      

-- 本月

SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';      

-- 下月

SELECT DATE_FORMAT( ADDDATE(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '下月初';
SELECT DATE_FORMAT( LAST_DAY(ADDDATE(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '下月末';