SQL查询日期和时间

SQL查询日期和时间

日期和时间类型

MySQL（Mariadb）支持的日期和时间类型有 DATETIME、TIMESTAMP、DATE、TIME、YEAR
日期时间类型占用空间显示格式用途DATETIME8字节YYYY-MM-DD hh:mm:ss混合日期和时间值TIMESTAMP4字节YYYY-MM-DD hh:mm:ss混合日期和时间值，时间戳DATE4字节YYYY-MM-DD日期值TIME3字节hh:mm:ss 或 hhh:mm:ss时间值或持续时间YEAR1字节YYYY年份值

日期和时间相关函数

• CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值。
• CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值。
• NOW() 和 SYSDATE() 两个函数作用相同，返回当前系统的日期和时间值。
• UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数。
• FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数。
• TO_DAYS() 提取日期值并返回自公元0年到现在的天数。
• DAY() 获取指定日期或时间中的天值。
• DATE() 获取指定日期或时间中的日期。
• TIME() 获取指定日期或时间中的时间。
• MONTH 获取指定日期中的月份。
• WEEK 获取指定日期是一年中的第几周。
• YEAR 获取年份。
• QUARTER 获取日期所在的季度值。
• YEARWEEK() 返回给定日期的年和周数（从 0 到 53 的数字）
• DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔。
• DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔。
• ADDTIME 时间加法运算，在原始时间上添加指定的时间。
• SUBTIME 时间减法运算，在原始时间上减去指定的时间。
• DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值。
• DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值。

日期和时间查询语句

查询今天的数据

select*from 表名 where to_days(时间字段名)= to_days(now());

查询昨天的数据

SELECT*FROM 表名 WHERE DATEDIFF(时间字段名,NOW())=-1;
或
SELECT*FROM 表名 WHERE TO_DAYS(NOW()) – TO_DAYS(时间字段名)=1;

查询本周的数据（从周日开始算起）

SELECT*FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d'))= YEARWEEK(now());

查询上周的数据（从周日开始算起）

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

查询7天内的数据

SELECT*FROM 表名 where DATE_SUB(CURDATE(),INTERVAL7DAY)<=date(时间字段名);

查询本月的数据

SELECT*FROM 表名 WHERE DATE_FORMAT(时间字段名, ‘%Y%m’ )= DATE_FORMAT( CURDATE(), ‘%Y%m’ );

查询30天内的数据

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

查询6个月内容的数据

SELECT*FROM 表名 WHERE 时间字段名 between date_sub(now(),interval6month)andnow();

查询本季度的数据

SELECT*FROM 表名 WHERE QUARTER(时间字段名)=QUARTER(now());

查询上季度的数据

SELECT*FROM 表名 WHERE QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval1 QUARTER));

查询本年的数据

SELECT*FROM 表名 WHEREYEAR(时间字段名)=YEAR(NOW());

查询上年的数据

SELECT*FROM 表名 WHEREyear(时间字段名)=year(date_sub(now(),interval1year));

指定时间段查询

SELECT*FROM 表名 WHERE(时间字段名)between'2022-10-18 00:00:00'and'2022-10-20 23:59:59';

举例：

统计当天登录的用户数
selectcount(distinct user_id)as'当天登录用户数'from
    login_record
where
    to_days(login_time)= to_days(now());
    
    
查询昨日登录用户的信息并按部门名称排序    
select    user_id,    user_name,    department_no,    departmen_name,    login_time,    ip_address
from
    login_record
where
    DATEDIFF(login_time,NOW())=-1orderby departmen_name ;

使用多种方法统计某一天的数据
 
SELECT*FROM login_record WHERE login_time between'2022-10-10 00:00:00'and'2022-10-10 23:59:59'SELECT*FROM login_record WHEREyear(login_time )=2022andmonth(login_time )=10andday(update_time )=10SELECT*FROM login_record WHERE login_time >'2022-10-10'and login_time <'2022-10-11'SELECT*FROM login_record WHERE( datediff ( login_time ,'2022-10-10')=0)