【sql那些事】时间处理的一揽子事
程序员文章站
2022-05-04 19:45:03
...
【sql那些事】时间处理的一揽子事
能用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(create_time, "+00:00", "+8:00") AS bj_create_time FROM test_table
其中字段 create_time
是 UTC
的时间,用 convert_tz()
函数可以转成北京时间。
当前时间
有时候需要获取最近7天的创建的数据,那么我们就需要对日期做一个对比,可以借助CURRENT_DATE()
来完成。
SELECT * FROM test_table WHERE create_time > CURRENT_DATE() - INTERVAL 7 DAY
current_date()
获取的是数据库默认设置时区的日期,即UTC
时区,如果create_time
是一个北京时间怎么办?
很简单,结合时区转化函数就可以将current_date()
转换成北京时间。。
SELECT * FROM test_table
WHERE create_time > CONVERT_TZ(CURRENT_DATE() - INTERVAL 7 DAY, "+00:00", "+08:00")
下面这个sql可以用来获取当天的数据,需要用DATE()
函数来获取时间字段的日期值。
SELECT * FROM test_table WHERE DATE(create_time) = CURRENT_DATE()
时间格式化
如果要按月进行统计怎么办?如果要把时间格式化成某一个特定的格式怎么做?不需要查出来后用程序处理,sql语句也能通通搞定。
有一个很常用的时序统计相关的需求,按照月统计新增用户数量并按照日期从小到大排序。
-- 格式化成月
SELECT
COUNT(*) AS cnt,
DATE_FORMAT(create_time, '%Y-%m') AS x
FROM test_table 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_table
GROUP BY x
ORDER BY x
上面出来的结果就是时间序列的统计结果,某年某月的某一天的某个时辰,都不是问题。
上一篇: 软件开发中的反模式
下一篇: 安卓自定义View——Matrix
推荐阅读
-
程序员应了解的那些事(16)C语言中利用setjmp和longjmp做异常处理 / 不要在C++中使用setjmp和longjmp
-
PostgreSQL服务过程中的那些事二:Pg服务进程处理简单查询三:获取内存快照
-
GreenPlum的那些事《六》——时间计算
-
【sql那些事】时间处理的一揽子事
-
【java】对数据库操作的那些事(包含数据库中的预处理)
-
Python字符串的处理那些事
-
Python字符串的处理那些事
-
说说watir-webdriver那些事(十二)table的处理
-
laya ui 中已经处理了的那些事
-
异常处理的那些事——try catch finally、throw、throws\自定义异常