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

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'