数据库时间相关操作——查询近30天及其他时间函数

sepy
64 阅读2分钟

1. 关键函数

  1. 返回当前时间或日期

    1. CURDATE()：返回当前的日期。

      • curdate()当前日期

    2. NOW():函数返回当前系统的日期和时间。

      • 当前时间+日期
      • SELECT NOW(),CURDATE(),CURTIME()

  2. 设置时间间隔

    • interval 7 day:间隔是7天

  3. 计算时间长度

    • TO_DAYS(‘1997-10-07′):从年份0开始到结束日期的天数

  4. 时间格式化

    • DATE_FORMAT() 函数：用于以不同的格式显示日期/时间数据。
    • DATE_FORMAT(date,format)

  5. 从指定日期减去指定时间间隔

    • DATE_SUB() 函数从日期减去指定的时间间隔。
    • DATE_SUB(date,INTERVAL expr type)

  6. 返回两日期的差异，返回月份

    • PERIOD_DIFF()函数返回两日期之间的差异。结果以月份计算
    • PERIOD_DIFF(*period1*, *period2*)

2. 查询近期的数据

  1. 几个小时内

    • date_sub减去距离当前时间的时间间隔
    DATE_SUB(NOW(),INTERVAL 5 HOUR)

  2. 今天

    • to_days计算时间长度
    • 传入的数据库时间字段=当前时间字段
    select * from 表名
where to_days(时间字段名) = to_days(now())

  3. 昨天

    • to_days时间长度小于等于一
    SELECT * FROM 表名 
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

  4. 7天

    • date_sub:时间字段，距离当前字段时间间隔小于等于
    SELECT * FROM 表名 
where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

  5. 近30天

    SELECT * FROM 表名 
where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

  6. 本月

    • DATE_FORMAT：字段统一格式显示
    SELECT * FROM 表名 
WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

  7. 上个月

    SELECT * FROM 表名 
WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ),date_format( 时间字段名, '%Y%m' ) ) =1

3. 近30天的数据查询

  1. 样式

    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as time ,count(*) as count
FROM channel
where DATE_SUB(NOW(), INTERVAL 30 DAY) <![CDATA[ <= ]]> date(create_time)
GROUP BY time

  2. 结构要点

    1. 将需要显示的内容格式化 DATE_FORMAT(create_time,'%Y-%m-%d')
    2. conut(*):计数统计
    3. as:重命名
    4. 当前时间和30天时间间隔的差值 小于等于 当前时间列值 DATE_SUB(NOW(), INTERVAL 30 DAY) <![CDATA[ <= ]]> date(create_time)
    5. 按照时间分组显示

  3. 核心

    • 分组按照重命名且格式化的时间，再去进行分组
    • 这样就会按照时间格式化后的格式去分组了

4. 构建虚拟表查询

  1. 核心

    • @i := -1：表示@i 自增i的初始值为-1
    • @i := @i + 1 ：表示序列号值自增1
    • SELECT 1 UNION ：建立虚拟临时短列表

  2. 样式

     SET @i := -1;
 SELECT t.time as name,IFNULL( u.total, 0 ) as value
 FROM(
     SELECT date_format( DATE_SUB( NOW( ), INTERVAL ( @i  := @i + 1 ) DAY ), '%Y-%m-%d' ) AS time
     FROM
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2
     WHERE
     @i <![CDATA[ <= ]]> 30 )t
 LEFT JOIN(
     SELECT DATE( user.create_time ) AS createTime,count(*) AS total
     FROM sys_user user
     GROUP BY DATE( user.create_time ) )u
 ON TO_DAYS( t.time ) = TO_DAYS( DATE( u.createTime ) )
 ORDER BY t.time;

5. 简单查询时间段

  1. 使用between and查询

    SELECT COUNT(*) FROM blacklist_botnet
WHERE ct_date BETWEEN '2020-11-03' AND '2020-11-04'
avatar
文章
阅读
粉丝