所有語(yǔ)句都是基于mysql的語(yǔ)法，能用一條sql語(yǔ)句搞定的就不要去寫(xiě)程序。

自動(dòng)設(shè)置記錄的創(chuàng)建和更新時(shí)間

為每一條記錄添加create_time和update_time是非常明智的選擇，分別表示當(dāng)前記錄第一次添加和最后一次更改的時(shí)間戳。

如果你不知道sql語(yǔ)句可以自動(dòng)完成這個(gè)事兒，那么就不可避免要在程序中手動(dòng)去設(shè)置這兩個(gè)字段的值。

  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

只要在create table中加上這兩個(gè)字段，剩下的事兒壓根不需要再操心了。

時(shí)區(qū)轉(zhuǎn)化

很多時(shí)候?yàn)榱顺绦蛱幚矸奖憬y(tǒng)一，表中某個(gè)字段的時(shí)間是utc時(shí)間，但是生成報(bào)表需要轉(zhuǎn)化成北京時(shí)間。

SELECT CONVERT_TZ(x, "+00:00", "+8:00") AS bj_x FROM t

其中字段x是utc的時(shí)間，用convert_tz 函數(shù)可以轉(zhuǎn)成北京時(shí)間。

當(dāng)前時(shí)間

有時(shí)候需要獲取最近3天的創(chuàng)建的數(shù)據(jù)，那么我們就需要對(duì)日期做一個(gè)對(duì)比，可以借助CURRENT_DATE()來(lái)完成。

SELECT * FROM test WHERE create_time > CURRENT_DATE() - INTERVAL 3 DAY

如果create_time是一個(gè)UTC的時(shí)間怎么辦，很簡(jiǎn)單，結(jié)合時(shí)區(qū)轉(zhuǎn)化函數(shù)就可以一步到位。

SELECT * FROM test 
WHERE create_time > CONVERT_TZ(CURRENT_DATE() - INTERVAL 3 DAY, "+08:00", "+00:00")

current_date獲取的是當(dāng)前時(shí)區(qū)的日期，需要轉(zhuǎn)換成utc的時(shí)間。

下面這個(gè)sql可以用來(lái)獲取當(dāng)天的數(shù)據(jù)，需要用DATE()函數(shù)來(lái)獲取時(shí)間字段的日期值。

SELECT * FROM test WHERE DATE(create_time) = CURRENT_DATE()

時(shí)間格式化

如果要按月進(jìn)行統(tǒng)計(jì)怎么辦？如果要把時(shí)間格式化成某一個(gè)特定的格式怎么做？不需要查出來(lái)后用程序處理，sql語(yǔ)句也能通通搞定。

有一個(gè)很常用的時(shí)序統(tǒng)計(jì)相關(guān)的需求，按照月統(tǒng)計(jì)新增用戶(hù)數(shù)量并按照日期從小到大排序。

-- 格式化成月
SELECT 
COUNT(*) AS cnt,
DATE_FORMAT(create_time, '%Y-%m') AS x 
FROM test GROUP BY x
GROUP BY x
ORDER BY x

-- 格式化成天
SELECT 
COUNT(*) AS cnt,
DATE_FORMAT(create_time, '%Y/%m/%d 00:00:00') AS x 
FROM test 
GROUP BY x
ORDER BY x

上面出來(lái)的結(jié)果就是時(shí)間序列的統(tǒng)計(jì)結(jié)果，某年某月的某一天的某個(gè)時(shí)辰，都不是問(wèn)題。

參考

mysql日期函數(shù)用法鏈接

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html