目錄

• mysql時(shí)間戳轉(zhuǎn)換
• 有以下3種方法
• 總結(jié)

mysql時(shí)間戳轉(zhuǎn)換

• select unix_timestamp(); 獲取當(dāng)前時(shí)間的時(shí)間戳

• SELECT FROM_UNIXTIME(1529999834); 時(shí)間戳轉(zhuǎn)換為北京時(shí)間

要得到正確的時(shí)間相減秒值

有以下3種方法

• 1、time_to_sec(timediff(t2, t1)),
• 2、timestampdiff(second, t1, t2),
• 3、unix_timestamp(t2) -unix_timestamp(t1)

SELECT AcceptTime,CreateTime, (AcceptTime-CreateTime) sec FROM `req_dlr` WHERE SellOrg="CC經(jīng)銷商88" AND CreateTime>="1527350400" AND CreateTime <= "1529942399"?AND AcceptTime <>"0" AND makeid = "36000000" AND ReqState <> "97";

此時(shí)用第3種方法，時(shí)間戳直接相減

3、timediff(time1,time2)，兩個(gè)時(shí)間相減，返回time差值，如圖

注意：timediff(time1,time2) 函數(shù)的兩個(gè)參數(shù)類型必須相同。

3.1、

MySQL （時(shí)間和秒互相轉(zhuǎn)換）：time_to_sec(time), sec_to_time(seconds)

select time_to_sec("01:00:05"); -- 3605 轉(zhuǎn)換為秒select sec_to_time(3605); -- "01:00:05"

3.2、

1、req_dlr表中AcceptTime,CreateTime兩個(gè)時(shí)間戳轉(zhuǎn)換為北京時(shí)間，然后只取時(shí)間

2、把取到的時(shí)間轉(zhuǎn)換為秒，相減后求和

SELECTsum(time_to_sec(M.A)- TIME_TO_SEC(M.C))FROM(SELECTdate_format(FROM_UNIXTIME(AcceptTime),"%H:%i:%S") A,date_format(FROM_UNIXTIME(CreateTime),"%H:%i:%S") CFROM`req_dlr`WHERECreateTime >= "1527350400"AND CreateTime <= "1529942399"AND AcceptTime <> "0"AND makeid = "36000000"AND ReqState <> "97") AS MWHEREM.A <= "19:00:00"AND M.A >= "09:00:00";

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持。