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

本文详细介绍了如何在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)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

冷环渊

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值