15-时间与字符串转换

1. 当前日期&时间

now() # 获取当前日期和时间 2019-11-12 18：18：57
curdate() # 当前日期 2019-11-12
curtime() # 当前时间 18：18：57
current_time() # 同curtime()，current_time
current_date() # 同curdate(),current_date
current_timestamp() # 同now()

2. 时间截取

date(expr) #  如果解析失败返回Null，返回日期类型date
time(expr) # 返回时间类型 time
year(expr) # 返回数字
month(expr)# 返回数字【1-12】
day(expr) # 返回数字【1-31】
hour(expr) # 返回数字【0-23】
minute(expr) # 返回数字【0-59】
last_day(date) # 返回最后一天 ,返回date类型 # 2018-04-30
to_days(expr) # 返回到公元0年一月一日的总天数
DATE_FORMAT(expr,pattern) # 时间截取，返回字符串
# expr :时间字符串，日期字符串或者日期时间类型。
# 对于截取年，月，日，时，分，秒返回的都是对应的字符串，因为时间日期类型mysql 本身就只提供了date,time ,datetime,timestamp,year这五种类型

3. 日期时间增减

ADDDATE(date,INTERVAL expr unit)  # 返回日期格式
# date 要操作的日期时间，可以是日期类型也可以是日期字符串
# INTERVAL: MySQL关键字 ，意思是间隔，间隙
# unit 操作的单元，年，月，日，时，分，秒对应YEAR，MONTH，DAY，HOUR，MINUTE，SECOND
# expr 想要增加或减少的时间单位。expr>0,增加，expr<0减少

SUBDATE(date,INTERVAL expr unit) #类似adddate()

4. 两个日期之间的差值

DATEDIFF(date1,date2)  # 两个日期之间的差值

5. 日期转化为字符串

DATE_FORMAT(date,'%Y-%m-%d %H:%i:%S') # 转为字符串
TIME_FORMAT(date,pattern) # 只能转化时间，不能转化日期
from_unixtime(timestamp,pattern) # 时间戳转为字符串

6. 字符串转时间

str_to_date(dateStr,pattern)
# dateStr :字符串日期表达式
# pattern ：匹配模式

7. 时间戳与时间转换

unix_timestamp([date]) # 将日期转化为时间戳，参数可以不写默认now()
# date 可以是日期时间类型 也可以是时间字符串 ‘2018-2-12’

from_unixtime(timestamp,'%Y-%m-%d %H:%i:%S') # 根据format格式化时间戳
# timestamp : 时间戳即 UNIX_TIMESTAMP()
# pattern 目标的模式字符串。使用format参数后返回的结果是 *** 字符串***。常用的时间格式化占位符如下更多参数请参考,
    %Y 年，四位数字
    %y 年，后两位数字
    %m 月 ，数字[1-12]
    %d 日，月份里的第几天，两位数字[1-31]
    %H 小时，24进制 [0-23]
    %h 小时，12进制[0-11]
    %i 分钟 [0-59]
    %s | %S 秒 0-59

8. 实战例子

• 获取目标时间的前一天凌晨 0 点到晚上 0 点，常用于获取前一天的历史数据

  比如：目标日期为 2021-01-05，那么获取的时间为 2021-01-04 00:00:00 ~ 2021-01-05 00:00:00

-- 取一开的开始时间
SELECT str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s');
-- 取第二天的开始时间
select DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY)
-- 取一天的结束时间
select DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY),INTERVAL -1 SECOND);

# 查询目标时间 大于等于 凌晨(昨天)00:00:00, 小于 晚上(今天)00:00:00
select TIME_TO_SEC(TIMEDIFF(targetDate, str_to_date(DATE_FORMAT(DATE_SUB(now(), INTERVAL 1 DAY), '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s'))) >= 0
select TIME_TO_SEC(TIMEDIFF(targetDate, str_to_date(DATE_FORMAT(now(), '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s'))) < 0;

• 获取各种所需时间范围

# 利用to_days函数查询今天的数据：
select * from 表名 where to_days(时间字段名) = to_days(now());
to_days函数：返回从0000年（公元1年）至当前日期的总天数。
# 昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 1
# 7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
# 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
# 本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m' ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m' )
# 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format( 时间字段名, ‘%Y%m' ) ) =1
# 本季度
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
# 上季度
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
# 本年
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
# 上年
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
# 当前这周
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
# 上周
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
# 当前月份
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
# 距离当前现在6个月
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
# 上个月
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from ` user ` where DATE_FORMAT(pudate, ‘ %Y%m ‘ ) = DATE_FORMAT(CURDATE(), ‘ %Y%m ‘ ) ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select *
from user
where MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())
select *
from [ user ]
where YEAR (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = YEAR (now())
and MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())
select *
from [ user ]
where pudate between 上月最后一天
and 下月第一天
where date(regdate) = curdate();
select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())
SELECT date( c_instime ) ,curdate( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30

​