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

冷环渊

已于 2023-09-27 23:39:37 修改

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： sql 数据库

于 2023-09-27 23:38:39 首次发布

本文链接：https://blog.youkuaiyun.com/doomwatcher/article/details/133365533

MySQL 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了如何在SQL中使用DATEADD,DATESUB,interval等函数进行日期区间筛选，包括半年、n天前、本周首日以及按小时段的数据提取，提供了解决时间相关查询问题的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 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)

取出指定时间 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)

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

看着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)