SQL查询日期和时间

日期和时间类型

MySQL（Mariadb）支持的日期和时间类型有 DATETIME、TIMESTAMP、DATE、TIME、YEAR

日期时间类型	占用空间	显示格式	用途
DATETIME	8字节	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4字节	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳
DATE	4字节	YYYY-MM-DD	日期值
TIME	3字节	hh:mm:ss 或 hhh:mm:ss	时间值或持续时间
YEAR	1字节	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(), INTERVAL 7 DAY) <= date(时间字段名);

查询本月的数据

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

查询30天内的数据

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

查询6个月内容的数据

SELECT * FROM 表名 WHERE 时间字段名 between date_sub(now(),interval 6 month) and now();

查询本季度的数据

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

查询上季度的数据

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

查询本年的数据

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

查询上年的数据

SELECT * FROM 表名 WHERE year(时间字段名)=year(date_sub(now(),interval 1 year));

指定时间段查询

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

举例：

统计当天登录的用户数
select
	count(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())=-1
order by 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 WHERE year(login_time ) = 2022 and month(login_time )= 10 and day(update_time ) = 10
 
SELECT * 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 )