sql语句时间处理那些事儿

有福

发布于 2019-01-03 15:50:13

1.2K0

发布于 2019-01-03 15:50:13

文章被收录于专栏：极客生活

所有语句都是基于mysql的语法，能用一条sql语句搞定的就不要去写程序。

自动设置记录的创建和更新时间

为每一条记录添加create_time和update_time是非常明智的选择，分别表示当前记录第一次添加和最后一次更改的时间戳。

如果你不知道sql语句可以自动完成这个事儿，那么就不可避免要在程序中手动去设置这两个字段的值。

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

只要在create table中加上这两个字段，剩下的事儿压根不需要再操心了。

时区转化

很多时候为了程序处理方便统一，表中某个字段的时间是utc时间，但是生成报表需要转化成北京时间。

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

其中字段x是utc的时间，用convert_tz 函数可以转成北京时间。

当前时间

有时候需要获取最近3天的创建的数据，那么我们就需要对日期做一个对比，可以借助CURRENT_DATE()来完成。

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

如果create_time是一个UTC的时间怎么办，很简单，结合时区转化函数就可以一步到位。

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

current_date获取的是当前时区的日期，需要转换成utc的时间。

下面这个sql可以用来获取当天的数据，需要用DATE()函数来获取时间字段的日期值。

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

时间格式化

如果要按月进行统计怎么办？如果要把时间格式化成某一个特定的格式怎么做？不需要查出来后用程序处理，sql语句也能通通搞定。

有一个很常用的时序统计相关的需求，按照月统计新增用户数量并按照日期从小到大排序。

-- 格式化成月
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

上面出来的结果就是时间序列的统计结果，某年某月的某一天的某个时辰，都不是问题。

参考

mysql日期函数用法链接

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2018.12.29 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度