mysql数据库按日期查询
日期和时间戳互换
FROM_UNIXTIME(unixtime, format) 将时间戳转换为日期时间字符串UNIX_TIMESTAMP(date) 将指定的日期或者日期字符串转换为时间戳DATE_FORMAT(date, format) 根据参数对date进行格式化
获取当前日期、时间
select now(); 或 select current_timestamp(); //获得当前时间 格式：2016-01-12 23:19:43
select curdate(); 或 select current_date(); //获得当前时间 格式： 2016-01-12
select current_date()+0; //获得当前时间 格式： 20160112
select curtime(); 或 select current_time(); //获得当前时间 格式：23:19:43以下“时间字段”是日期时间格式，如果是时间戳，需要用FROM_UNIXTIME()转换
查询N天内记录
当天的记录
select * from 表名 where to_days(时间字段名) = to_days(now());
select * from 表名 where date(时间字段名) = date(now());
昨天的记录
select * from 表名 where to_days(now())-to_days(时间字段名)=1;
7天的记录
select * from 表名 where date_sub(curdate(),interval 7 day)<=date(时间字段名);
1个月的记录
select * from 表名 where date_sub(curdate(),interval 1 month)<=date(时间字段名);
最近6个月的记录
select * from 表名 where 时间字段名 between date_sub(curdate(),interval 6 month) and curdate();查询本周、本月、本年的记录
本周
SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW());
本周（以周一开始，时间字段为时间格式）
SELECT * FROM 表名 WHERE YEARWEEK(时间字段名,1) = YEARWEEK(NOW(),1);
本月
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
本年
SELECT * FROM 表名 WHERE YEAR(时间字段名)=YEAR(NOW());查询上周、上月、去年的记录
上周
SELECT * FROM 表名 WHERE YEARWEEK(时间字段名) = YEARWEEK(NOW() - INTERVAL 1 WEEK); //周日-周六
SELECT * FROM 表名 WHERE YEARWEEK(时间字段名, 1) = YEARWEEK(NOW() - INTERVAL 1 WEEK, 1); //周一-周日
SELECT * FROM 表名 WHERE 时间字段名 > DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+7 DAY) AND 时间字段名 <= DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW()) DAY); //周日-周六
SELECT * FROM 表名 WHERE 时间字段名 > DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+6 DAY) AND 时间字段名 <= DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY); //周一-周日
上月
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y-%m') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
SELECT * FROM 表名 WHERE MONTH( 时间字段名 ) = MONTH( DATE_SUB(CURDATE(),INTERVAL 1 MONTH )) AND YEAR( 时间字段名 ) = YEAR( DATE_SUB(CURDATE(),INTERVAL 1 MONTH ));
去年
SELECT * FROM 表名 WHERE YEAR(时间字段名) = YEAR( DATE_SUB(CURDATE(),INTERVAL 1 MONTH ));按季度汇总
SELECT CONCAT(YEAR(payment_dt), '-Q',QUARTER(payment_dt)) as qt_year,
COUNT(*) as Nos, SUM(amount) as total, AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year财政年度 Fiscal Year
历年度制
日历年度制,是指财政年度的起止期与年历始末相同，即公历1月1日起至12月31日止。使用历年制有中国、德国、法国、波兰、匈牙利、朝鲜、南斯拉夫等。
跨年度制
跨日历年度制，是指财政年度起止期与年历始末不相同，如英国、奥地利、日本、加拿大、印度等国家和地区是自4月1日起至次年3月31日止；瑞典、埃及、澳大利亚、巴基斯坦、孟加拉国、苏丹等国家是自7月1日起至次年6月30日止；美国在1976年以前是自7月1日起至次年6月30日止，1976年以后改为自10月1日起至次年9月30日止。由10月1日起的还有：泰国、尼日利亚等。沙特阿拉伯、尼泊尔等国家的财政年度则是实行浮动制度，沙特阿拉伯根据Hajra历编制预算,其每一年度预算的起止日期和时间周期长短是不相同的。
示例代码来源 https://www.plus2net.com/sql_tutorial/date_quarter.php
SQL Dump for plus2_bills table
CREATE TABLE IF NOT EXISTS `plus2_bills` (
`bill_no` varchar(10) NOT NULL,
`payment_dt` date NOT NULL,
`amount` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `plus2_bills`
--
INSERT INTO `plus2_bills` (`bill_no`, `payment_dt`, `amount`) VALUES
('34521', '2015-03-04', 3412),
('56732', '2015-03-09', 8796),
('67598', '2015-04-11', 5438),
('87905', '2015-04-24', 5734),
('37125', '2015-09-10', 4378),
('98074', '2015-09-14', 7684),
('89654', '2015-10-09', 4536),
('85943', '2015-10-16', 3786),
('45678', '2016-02-11', 6754),
('36213', '2016-02-18', 7658),
('76584', '2016-06-09', 3543),
('13412', '2016-06-24', 7856),
('89764', '2016-07-14', 3456),
('45341', '2016-07-27', 5647),
('35643', '2016-10-13', 4532),
('98076', '2016-10-21', 6756),
('45678', '2017-01-03', 4563),
('67543', '2017-01-12', 4532),
('34561', '2017-05-10', 5674),
('89765', '2017-05-19', 5645),
('45342', '2017-08-17', 4512),
('89792', '2017-08-29', 5674),
('67831', '2017-12-07', 4535),
('76894', '2017-12-22', 5467),
('89671', '2018-03-02', 4567),
('89023', '2018-03-12', 4538);4月1日开始的财年
SELECT DATE_FORMAT( payment_dt, '%Y-%M-%d' ) AS DATE,
CASE WHEN QUARTER(payment_dt)=1
THEN CONCAT(YEAR(payment_dt)-1, '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt),'-Q',QUARTER(payment_dt)-1)
END AS qt_year
FROM `plus2_bills`4月1日开始的财年，形如 “2017-18 - Q3”的显示 YYYY-YY-Q format
SELECT DATE_FORMAT( payment_dt, '%Y-%M-%d' ) AS DATE,
CASE WHEN quarter(payment_dt)=1
THEN CONCAT(YEAR(payment_dt)-1, '-',date_format(payment_dt,'%y'), ' - Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt),'-',date_format(payment_dt,'%y') +1,' - Q',QUARTER(payment_dt)-1)
END AS qt_year
FROM `plus2_bills`10月1日开始的财年
SELECT DATE_FORMAT( payment_dt, '%Y-%M-%d' ) AS DATE,
CASE WHEN QUARTER( payment_dt ) !=4
THEN CONCAT( YEAR( payment_dt ) -1, '-Q', QUARTER( payment_dt ) +1 )
ELSE CONCAT( YEAR( payment_dt ) , '-Q', QUARTER( payment_dt )-3 )
END AS qt_year
FROM `plus2_bills` 10月1日开始的财年，形如 “2017-18 - Q3”的显示 YYYY-YY-Q format
SELECT DATE_FORMAT( payment_dt, '%Y-%M-%d' ) AS DATE,
CASE WHEN quarter(payment_dt)!=4
THEN CONCAT(YEAR(payment_dt)-1, '-',date_format(payment_dt,'%y'), ' - Q',QUARTER(payment_dt)+1)
ELSE concat(YEAR(payment_dt),'-',date_format(payment_dt,'%y')+1,' - Q',QUARTER(payment_dt)-3)
END AS qt_year
FROM `plus2_bills`按季度汇总(4月1日-3月31日)
SELECT
CASE WHEN QUARTER(payment_dt)=1
THEN CONCAT(YEAR(payment_dt)-1, '-Q',QUARTER(payment_dt)+3)
ELSE concat(YEAR(payment_dt),'-Q',QUARTER(payment_dt)-1)
END AS qt_year ,
COUNT(*) as Nos, SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year按季度汇总(7月1日-6月30日)
SELECT
CASE WHEN QUARTER( payment_dt ) <=2
THEN CONCAT( YEAR( payment_dt ) -1,'-Q', QUARTER( payment_dt ) +2 )
ELSE CONCAT( YEAR( payment_dt ) , '-Q', QUARTER( payment_dt )-2 )
END AS qt_year,
COUNT(*) as Nos,SUM(amount) as total,AVG(amount) as average
FROM `plus2_bills` GROUP BY qt_year本文最后更新时间：2020年11月8日