sql日期操作

获取时间

获取当前时间

SELECT NOW();# 2022-03-28 07:23:14

获取当前日期

SELECT CURRENT_DATE();# 2022-03-28

获取当前时间

SELECT CURRENT_TIME();# 07:24:57

时区问题

查看sql时区

Mysql的默认时区是UTC（协调世界时，又称世界统一时间，世界标准时间，国际协调时间）

show variables like "%time_zone%";

设置mysql时区

• 永久的修改

  修改mysql的配置文件my-default.ini，添加：default-time-zone=’+08:00’，重启mysql生效,注意一定要在 [mysqld] 之下加 ，否则会出现 unknown variable ‘default-time-zone=+8:00’

  my-default.ini文件内：
  [mysqld] 
  default-time-zone='+08:00'
  

• 临时的修改

  执行mysql命令 set global time_zone=’+08:00’，立即生效，重启mysql后失效

  set time_zone = '+8:00';
  set global time_zone='+08:00';
  

日期格式化

日期转指定格式字符串

SELECT DATE_FORMAT('2022-01-08 22:23:01', '%Y%m%d%H%i%s');# 20220108222301
说明：
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位 ★★★
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31) ★★★
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12) ★★★ month
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)★★★
%k 小时(0……23)
%h 小时(01……12) 
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59) ★★★ minit
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59) ★★★
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天,查询指定日期属于当前年份的第几个周 ★★★
%u 星期(0……52), 这里星期一是星期的第一天

示例

# 日期格式化
select date_format(now(),'%Y%m%d%H%i%s');# 20220328074552
# 获取当前是星期几
select date_format(now(),'%Y%m%W');# 202203Monday
# 查看当前属于一年中的第几个周 以周末作为一个循环
select date_format(now(),'%Y%U'); #202213

字符串转日期

# 日期格式与表达式格式一致即可
SELECT STR_TO_DATE('06/01/2022', '%m/%d/%Y'); # 2022-06-01
SELECT STR_TO_DATE('20220108090109', '%Y%m%d%H%i%s'); # 2022-01-08 09:01:09

日期间隔

增加日期间隔

语法：

DATE_ADD(date,INTERVAL expr type)
-- date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔
/* type的参数列表
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
*/

# 间隔单位可以是DAY MONTH MINUTE WEEK YEAR SECOND HOUR
SELECT DATE_ADD(NOW(),INTERVAL 10 DAY); # 2022-04-07 07:53:06
SELECT DATE_ADD(NOW(),INTERVAL -10 DAY); # 2022-03-18 07:56:26
SELECT DATE_ADD('2022-02-06 22:47:17',INTERVAL 2 MONTH); # 2022-04-06 22:47:17

与增加一个日期间隔相似的就是减去一个日期间隔，他们的用法是一样的。

示例

SELECT DATE_SUB(NOW(),INTERVAL 3 DAY); # 2022-03-25 08:00:50
SELECT DATE_SUB('2022-02-06 22:47:17',INTERVAL 2 MONTH); # 2021-12-06 22:47:17

日期相差的天数

select datediff('2022-01-06','2021-12-28'); -- 9
-- datediff(date1,date2) 功能：返回两个日期之间的天数

日期相差小时

select timediff('2022-01-06 08:08:08', '2021-12-28 09:00:00'); -- 08:08:08

计算时间差之TIMESTAMPDIFF函数

TIMESTAMPDIFF(unit,begin,end);
/*
TIMESTAMPDIFF函数返回begin-end的结果，begin，end可以是date或datetime类型的表达式；
unit参数是确定begin-end 的结果的单位，表示为整数，其取值可以有：
1. MICROSECOND
2. SECOND
3. MINUTE
4. HOUR
5. DAY
6. WEEK
7. MONTH
8. QUARTER
9. YEAR
*/

星期操作

返回date的星期索引

# 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
SELECT DAYOFWEEK(NOW())-1;

# 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)
SELECT WEEKDAY(NOW())+1

其他操作

# 获取日
SELECT DAYOFMONTH(NOW());# 6
# 获取月份
SELECT MONTH(NOW());# 1
# 获取星期几
SELECT DAYNAME(NOW());# Thursday
# 获取第几季度
SELECT QUARTER(NOW());# 2022/1/6 --> 1