写给自己

常常需要对数据库的时间进行检索，有没有快的方案简介的SQL语句可以实现，答案是可以的。

创作时间

81213 18：20：00

作者

gaox

正文

举例查询当天、本周、本月、本季度、本年

当天

select curdate();

select id,reatec_date from balance_record where
to_days(reatec_date) = to_days(now());

本周

SELECT id,reatec_date FROM balance_record WHERE
YEARWEEK(date_format(reatec_date,'%Y-%m-%d')) = YEARWEEK(now());

本月

第一天
select DATE_ADD(curdate(),interval -day(curdate())+1 day);
最后一天
select last_day(curdate());
下一月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);
本月多少天
SELECT id,reatec_date FROM balance_record WHERE
DATE_FORMAT(reatec_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

本季度

SELECT id,reatec_date FROM balance_record WHERE
QUARTER(reatec_date)=QUARTER(now());

本年

SELECT id,reatec_date FROM balance_record WHERE
YEAR(reatec_date)=YEAR(now());

举例查询上/下 i 天、周、月、季度、年

昨天

select id,reatec_date from balance_record where
to_days(now()) - to_days(reatec_date) = 1;

上一周

SELECT id,reatec_date FROM balance_record WHERE
YEARWEEK(date_format(reatec_date,'%Y-%m-%d')) = YEARWEEK(now())-1;

上月

SELECT id,reatec_date FROM balance_record WHERE
PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(reatec_date,'%Y%m')) = 1;

上个季度

SELECT id,reatec_date FROM balance_record WHERE
QUARTER(reatec_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

上一年

SELECT id,reatec_date FROM balance_record WHERE
YEAR(reatec_date)=YEAR(DATE_SUB(now(),INTERVAL 1 YEAR));

查询时间段

近7天(前)

SELECT id,reatec_date FROM balance_record where
DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(reatec_date);
| 备注：这里应该是天数-1，从0开始|

近30天

SELECT id,reatec_date FROM balance_record where
DATE_SUB(CURDATE(), INTERVAL 29 DAY) <= date(reatec_date);

指定时间查询

select count(*) from balance_record where
reatec_date >='2010-03-01 00:00:00' and reatec_date <'2020-03-02 00:00:00'

常用函数

interval的说明：

1、当函数使用时，即interval(),为比较函数，如：interval(10,1,3,5,7); 结果为4；

原理：10为被比较数，后面1,3,5,7为比较数，将后面四个依次与10比较，看后面数字组有多少个少于10，则返回其个数。前提是后面数字组为从小到大排列，否则返回比他大的数字之前的个数。

2、当关键词使用时，表示为设置时间间隔，常用在date_add()与date_sub()函数里，如：interval 1 day ，解释为将时间间隔设置为1天。

MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

-- 获取当前日期
select curdate();
-- 获取当月最后一天。
select last_day(curdate());
-- 获取上月最后一天。
select last_day(date_sub(curdate(),interval 1 month)) as per_last_day;
-- 获取本月第一天
select DATE_ADD(curdate(),interval -day(curdate())+1 day);
-- 获取下个月的第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);
-- 获取当前月的天数
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),
    DATE_ADD(curdate(),interval -day(curdate())+1 day));

其他函数

SET @this_time = NOW();

SELECT
    @this_time AS 'datetime',
    YEAR ( @this_time ) AS 'year',
    QUARTER ( @this_time ) AS 'quarter',
    MONTH ( @this_time ) AS 'month',
    DAY ( @this_time ) AS 'day',
    HOUR ( @this_time ) AS 'hour',
    MINUTE ( @this_time ) AS 'minute',
    SECOND ( @this_time ) AS 'second';

+---------------------+------+---------+-------+-----+------+--------+--------+
| datetime            | year | quarter | month | day | hour | minute | second |
+---------------------+------+---------+-------+-----+------+--------+--------+
| 2020-05-20 10:12:03 | 2020 |       2 |     5 |  20 |   10 |     12 |      3 |
+---------------------+------+---------+-------+-----+------+--------+--------+

修改mysql数据库时区

经常出现本身在东八区（北京时间）而数据库存储的时间却是0时区的时间，在时间筛查时就会出现问题，我们可以通过以下手段将时间设置会来。

查看系统时间

select curtime(); -- 当前时间
select now();   -- 当前日期+时间

查看系统时区设置

show variables like "%time_zone%";

查询结果说明：
time_zone说明mysql使用system的时区
system_time_zone说明system使用CST时区(CST可视为美国、澳大利亚、古巴或中国的标准时间。)

1、在命令行修改时区

set global time_zone = '+8:00';  -- 修改mysql全局时区为北京时间(东八区)
set time_zone = '+8:00';  -- 修改当前会话时区
flush privileges;  -- 立即生效

2、在配置文件my.cnf配置文件修改时区

修改 vim /etc/my.cnf

在[mysqld]区域中加上
default-time_zone = '+8:00'

重启mysql

/etc/init.d/mysqld restart  -- 重启mysql使新时区生效

3、查询前设置

如果不方便重启mysql，又想临时解决时区问题，可以通过php或其他语言在初始化mysql时初始化mysql时区
这里，以php为例，在mysql_connect()下使用mysql_query(“SET time_zone = ‘+8:00′”)。
这样可以在保证你不重启的情况下改变时区。但是mysql的某些系统函数还是不能用如：now()。这句，还是不能理解。

结束语