sql小技巧：日期区间和格式化操作

sql小技巧

日期区间和格式化操作

根据不同的日期要求取得数据

主要是对聚合函数 DATEADD()、DATESUB() 和关键字 interval的使用

筛选指定时间半年

思路： 其实就是用当前时间减去五个月

SELECT date,user_id FROM TABLENAME
WHERE 
from_unixtime(timestamp / 1000) >= DATE_SUB(CURDATE(), interval 5 month)
1
2
3

筛选指定时间往前 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 TABLENAME
 WHERE
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)
1
2
3
4
5

取出指定时间 n周的 第一天 作为本周的数据头

函数说明：

• timestamp / 1000：将时间戳除以1000，转换为秒数。
• from_unixtime()：将秒数转换为Unix时间戳。
• date_sub()：计算指定时间减去的时间间隔。
• dayofweek(from_unixtime(timestamp / 1000)) - 2 day：计算指定时间的星期几减去2天，用于计算日期偏移量。
• DATE_FORMAT()：将日期格式化为指定的格式，其中%Y表示年份，%c表示月份的英文缩写，%d表示日期。

使用 dayofweek 拿到当前时间的本周第一天并且重写格式

 SELECT
 DATE_FORMAT(date_sub(from_unixtime(timestamp / 1000), interval
  dayofweek(from_unixtime(timestamp / 1000)) - 2 day), '%Y%c%d') AS date
    , user_id
    FROM TABLENAME
    WHERE
    from_unixtime(timestamp / 1000) >=
    date_sub(from_unixtime(#{指定时间} / 1000), interval 10 week)
1
2
3
4
5
6
7
8

指定天数每个小时段的数据

看着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_id
    FROM TABLENAME
    WHERE
    from_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)
1
2
3
4
5
6
7
8
9