sql小技巧
日期区间和格式化操作
根据不同的日期要求取得数据
主要是对聚合函数 DATEADD()、DATESUB() 和关键字 interval的使用
筛选指定时间半年
思路: 其实就是用当前时间减去五个月
SELECT date,user_id FROM TABLENAME
WHERE
from_unixtime(timestamp / 1000) >= DATE_SUB(CURDATE(), interval 5 month)
筛选指定时间往前 n 天
思路: 依旧是对时间的计算上做手脚 ,但是要记得控制左右边界 不然会出现查询数据缺失和混乱问题 如
from_unixtime({指定时间} / 1000)+INTERVAL 1 DAY - INTERVAL 1 SECOND
如果直接使用当前时间 获取的时候是 23-9-27 00:00:00
这个时候当前就不算在范围内了,我们需要利用 INTERVAL 1 DAY - INTERVAL 1 SECOND
取到 23-9-27 23:59:59
保证数据正常显示
SELECT date,user_id FROM TABLENAMEWHERE
from_unixtime(timestamp / 1000) >= DATE_SUB(from_unixtime({指定时间} / 1000), interval 20 day)
AND
from_unixtime({指定时间} / 1000)+INTERVAL 1 DAY - INTERVAL 1 SECOND >= from_unixtime(`timestamp`/ 1000)
取出指定时间 n周的 第一天 作为本周的数据头
函数说明:
timestamp / 1000
:将时间戳除以1000,转换为秒数。from_unixtime()
:将秒数转换为Unix时间戳。date_sub()
:计算指定时间减去的时间间隔。dayofweek(from_unixtime(timestamp / 1000)) - 2 day
:计算指定时间的星期几减去2天,用于计算日期偏移量。DATE_FORMAT()
:将日期格式化为指定的格式,其中%Y
表示年份,%c
表示月份的英文缩写,%d
表示日期。
使用 dayofweek
拿到当前时间的本周第一天并且重写格式
SELECTDATE_FORMAT(date_sub(from_unixtime(timestamp / 1000), intervaldayofweek(from_unixtime(timestamp / 1000)) - 2 day), '%Y%c%d') AS date, user_idFROM TABLENAMEWHEREfrom_unixtime(timestamp / 1000) >=date_sub(from_unixtime(#{指定时间} / 1000), interval 10 week)
指定天数每个小时段的数据
看着sql 长, 其实就是对数据的时间的格式化,通过时分秒去判断边界
-
SELECT DATE_FORMAT(from_unixtime(timestamp / 1000), '%H') AS date, user_id
:将时间戳转换为Unix时间戳后,再将其格式化为小时(24小时制),并命名为"date",同时选择用户ID列。 -
WHERE from_unixtime(timestamp / 1000) > str_to_date(...) AND DATE_ADD(...) > from_unixtime(timestamp / 1000)
:设置查询条件。
str_to_date(DATE_FORMAT(from_unixtime(${指定时间} / 1000), '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s')
:将指定时间(以秒为单位)转换为日期格式,并命名为"指定时间"。DATE_ADD(str_to_date(...), INTERVAL 1 DAY)
:在指定时间上添加1天的时间间隔。DATE_ADD(..., INTERVAL -1 SECOND)
:在上一步的结果上再减去1秒的时间间隔。> from_unixtime(timestamp / 1000)
:筛选出时间戳大于上一步结果的时间戳。
SELECT DATE_FORMAT(from_unixtime(timestamp / 1000), '%H') AS date, user_idFROM TABLENAMEWHEREfrom_unixtime(timestamp / 1000) >str_to_date(DATE_FORMAT(from_unixtime(${指定时间} / 1000), '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s')AND DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(from_unixtime(${指定时间} / 1000), '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY), INTERVAL -1 SECOND) > from_unixtime(timestamp / 1000)