2019-04-21 mysql 时间函数
在Linux中字段名、数据库名和表名要区分大小写，比如下时间函数等不区分大小写
- MySQL服务器中的三种时区设置：
①系统时区---保存在系统变量system_time_zone
②服务器时区---保存在全局系统变量global.time_zone
③每个客户端连接的时区---保存在会话变量session.time_zone
mysql> show variables like 'system_time_zone';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| system_time_zone | CST |
+------------------+-------+
1 row in set (0.00 sec)
mysql> select @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +08:00 | +08:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
时区相关简介：
show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
UTC：本初子午线(即经度0度)上的时间.北京时间比UTC时间早8小时。
GMT：是格林尼治平时： UTC 基本上等于GMT 。
CST ：可以代表如下 4 个不同的时区：美国，澳大利亚，中国，古巴
Central Standard Time (USA) UTC-6:00
Central Standard Time (Australia) UTC+9:30
China Standard Time UTC+8:00
Cuba Standard Time UTC-4:00
SYSTEM 表示使用“OS时区”；
临时生效的修改MySQL时区---》修改global变量（不用重启）
set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间
set time_zone = '+8:00'; ##修改当前会话时区
flush privileges; #立即生效
通过修改my.cnf配置文件来修改时区 –》重启生效
vim /etc/my.cnf ##在[mysqld]区域中加上
default-time_zone = '+8:00'
/etc/init.d/mysqld restart ##重启mysql使新时区生效
- 时间函数介绍
注：日期—》指的是年月日；
时间—》指的是时分秒；
日期+时间—》年月日，时分秒。
ODBC标准：1 = 星期日，2 =星期一...7=星期六
第一类：获取当前时间相关的情况
（1）now() ： 当前日期+时间 select now(); // 2019-04-17 15:23:51
（2）curdate() ：当前日期 select curdate(); //2019-04-17
（3）curtime() : 当前时间 select curtime() //15:23:51
区别：
mysql> select curdate(),curdate()+0;
+------------+-------------+
| curdate() | curdate()+0 |
+------------+-------------+
| 2017-03-24 | 20170324 |
+------------+-------------+
第二类：将日期时间的格式进行—》周相关
（1）dayofweek(日期) : 将日期转换为周几 select dayofweek(‘2019-4-17'); // 3
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 4 |
+------------------+ 即：周三。星期日为1.
（2）weekday(日期时间) : 将日期时间专为周几 select WEEKDAY(now()); //4
（3）week(date,first): 将日期转一年的第几周
可选参数2：first的作用—》是周日与周一谁作为一周的开始
0表示周日是开始（默认），1表示周一是开始
select week('2019-04-17'); // 15 即：第七周—》等价于select week('2019-04-17',0);
select week('2019-04-17',1); //16
（4） dayname(日期 ): 将日期转换为星期几(英文名返回)
select dayname('1998-02-05'); //Thursday
第三类：将日期时间的格式进行---》天相关转换
（1）dayofmonth(日期) : 获取一月的第几天(1-31 范围) select dayofmonth('2019-04-17'); //17
（2）dayofyear(日期) : 获取一年的第几天（1-366 范围）select dayofyear('2019-04-17'); //107
（3）to_days(日期) 转换为天数
mysql> SELECT TO_DAYS(now());
+----------------+
| TO_DAYS(now()) |
+----------------+
| 737531 |
+----------------+
即：从0年开始 到1997年10月7号之间的天数。
第四类：将日期时间的格式进行---》月，季度，年相关转换
（1） month(日期): 获取日期中的月份值 select month('2019-04-17'); //4
（2） monthname(日期)：获取日期中英文月份 select monthname('2019-04-17') // April
（3） quarter(日期): 获取日期为第几季度 select quarter('2019-04-17'); //2
mysql> select quarter(now());
+----------------+
| quarter(now()) |
+----------------+
| 2 |
+----------------+
（4） year(日期) : 获取日期的年份（范围1000-9999） select year(now()); //2019
第五类：将时间的格式进行---》时，分相关转换，附件：日期，时间
（1）hour（now()）
（2）minute（now()）
（3）second(now()); -- 秒
（4）microsecond(now()); -- 微秒
mysql> select hour(now()), minute(now()), second(now()),microsecond(now());
+-------------+---------------+---------------+--------------------+
| hour(now()) | minute(now()) | second(now()) | microsecond(now()) |
+-------------+---------------+---------------+--------------------+
| 17 | 23 | 29 | 0 |
+-------------+---------------+---------------+--------------------+
（5）date(now()) -- 日期
（6）time(now()) -- 时间
mysql> select date(now()), time(now());
+-------------+-------------+
| date(now()) | time(now()) |
+-------------+-------------+
| 2019-04-17 | 17:24:36 |
+-------------+-------------+
第六类：与时间戳相关转换
（1） unix_timestamp(now()); 时间转时间戳
SELECT UNIX_TIMESTAMP() -- 1509372410
SELECT UNIX_TIMESTAMP(NOW()) -- 1509372410
SELECT UNIX_TIMESTAMP('2017-10-30 22:06:50') -- 1509372410
（2）from_unixtime(1451997924); 时间戳 转时间
SELECT FROM_UNIXTIME(1509372410) -- 2017-10-30 22:06:50
SELECT FROM_UNIXTIME(1509372410,'%Y/%m/%d %H:%i:%s') -- 2017/10/30 22:06:50
mysql> SELECT UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
| 1555493183 |
+-----------------------+
mysql> select from_unixtime(UNIX_TIMESTAMP(NOW()));
+--------------------------------------+
| from_unixtime(UNIX_TIMESTAMP(NOW())) |
+--------------------------------------+
| 2019-04-17 17:27:05 |
+--------------------------------------+
第七类：日期时间计算函数
（1）date_add(date, interval expr unit) 为日期增加一个时间间隔
（2）date_sub(date, interval expr unit) 为日期减去一个时间间隔
用途：常用于时间比较，比如执行删除多少天之前的日志。
select date_add(now(), interval 1 day); -- add 1 day
select date_add(now(), interval 1 hour); -- add 1 hour
select date_add(now(), interval 1 minute); -- ...
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 microsecond);
select date_add(now(), interval 1 week);
select date_add(now(), interval 1 month);
select date_add(now(), interval 1 quarter);
select date_add(now(), interval 1 year);
select date_add(now(), interval -1 day); -- sub 1 day
date_sub()用法同上。
mysql> select date_add(now(), interval 1 day),date_sub(now(), interval 1 day);
+---------------------------------+---------------------------------+
| date_add(now(), interval 1 day) | date_sub(now(), interval 1 day) |
+---------------------------------+---------------------------------+
| 2019-04-18 17:29:05 | 2019-04-16 17:29:05 |
+---------------------------------+---------------------------------+
问题：上面参数1 使用日期 与 日期+时间，返回值有区别吗？
mysql> select date_add('2019-04-17', interval 1 day),date_sub('2019-04-17', interval 1 day);
+----------------------------------------+----------------------------------------+
| date_add('2019-04-17', interval 1 day) | date_sub('2019-04-17', interval 1 day) |
+----------------------------------------+----------------------------------------+
| 2019-04-18 | 2019-04-16 |
+----------------------------------------+----------------------------------------+
扩展： interval是间隔类型关键字
expr是一个表达式，对应后面的类型
unit是时间间隔的单位(间隔类型)（20个），如下：
HOUR 小时
MINUTE 分
SECOND 秒
MICROSECOND 毫秒
YEAR 年
MONTH 月
DAY 日
WEEK 周
QUARTER 季
YEAR_MONTH 年和月
DAY_HOUR 日和小时
DAY_MINUTE 日和分钟
DAY_ SECOND 日和秒
HOUR_MINUTE 小时和分
HOUR_SECOND 小时和秒
MINUTE_SECOND 分钟和秒
最后：日期格式化函数—》DATE_FORMAT(date, format) 函数根据format字符串格式化date值。
SELECT DATE_FORMAT('2017-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'); //
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S');
+-------------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S') |
+-------------------------------------------+
| 2019-04-18 10:12:21 |
+-------------------------------------------+
mysql> select date_format(now(), '%Y%m%d') as datetime;
+----------+
| datetime |
+----------+
| 20190418 |
+----------+
注意：as显示别名。
png1..png
用途举例：
- 数据库存入的时间为时间戳，查询时根据时区转换为时区时间。
- 数据库查询是，根据时间做查询条件。
- 数据库删除时，删除N天之前的日志。
