一、获取当前时刻的数据
1.获取当前时刻的时间和日期:
select now()
2.获取当前时刻的日期:
*形式:2019-12-25
select curdate()
select data(now())
*形式:2019
select year(now())
*形式:12
select month(now())
*形式:25
select day(now())
3.获取当前时刻的时间:
*形式:22:47:37
select curtime()
select time(now())
*形式:22
select hour(now())
*形式:47
select minute(now())
*形式:37
select second(now())
4.获取当前时刻所属的周数:
*将全年分为52周
select weekofyear(now())
*获取当天是一周中的星期几
select dayofweek(now())
5.获取当前时刻所属的季度:
select
quarter("2019-01-01") as quarter_1
,quarter("2019-04-01") as quarter_2
,quarter("2019-07-01") as quarter_3
,quarter("2019-010-01") as quarter_4
*获取当天是一周中的星期几
select dayofweek(now())
二、日期和时间格式转换
1.date_format()函数:
date_format(datetime,format)
*dateform:要转换的具体的日期和时间
format:要转换的格式
*举例:以4位数字表示年;以01-12的形式表示的月;以01-31的形式表示某月中的第几天
select date_format("2019-12-25 22:47:37","%Y-%m-%d")
*结果:2019-12-25
2.extract()函数:
extract(unit from datetime)
*datetime:具体的日期和时间
unit:要从datetime中返回的单独的部分(年、月、日、小时、分钟、秒、周数)
*举例:
select
extract(year from "2019-12-25 22:47:37") as col1
,extract(month from "2019-12-25 22:47:37") as col2
,extract(day from "2019-12-25 22:47:37") as col3
*结果:2019 12 25
三、日期和时间运算
1.向后偏移日期和时间:date_add(date,interval num unit)
*date:当前的日期或当前的日期和时间
interval:固定的参数
num:相当于在今天日期和时间的基础上加x天
unit:要加的单位(天、月、年)
*举例1:
select
"2019-01-01" as col1
,date_add("2019-01-01",interval 7 year) as col2
,date_add("2019-01-01",interval 7 month) as col3
,date_add("2019-01-01",interval 7 day) as col4
*结果:2019-01-01 2026-01-01 2019-08-01 2019-01-08
*举例2:
select
"2019-01-01 01:01:01" as col1
,date_add("2019-01-01 01:01:01",interval 7 hour) as col2
,date_add("2019-01-01 01:01:01",interval 7 minute) as col3
,date_add("2019-01-01 01:01:01",interval 7 second) as col4
*结果:2019-01-01 01:01:01 2019-01-01 08:01:01 2019-08-01 01:08:01 2019-01-08 01:01:08
2.向前偏移日期和时间:date_sub(date,interval num unit)
*举例1:
select
"2019-01-01" as col1
,date_sub("2019-01-01",interval 7 year) as col2
,date_sub("2019-01-01",interval 7 month) as col3
,date_sub("2019-01-01",interval 7 day) as col4
*结果:2019-01-01 2012-01-01 2018-06-01 2018-12-25
3.两个日期之间作差:datediff(end_date,start_date)
select datediff("2019-01-07","2019-01-01")
*结构钢:6
4.两个日期之间的比较:
select
"2019-01-01" > "2019-01-02" as col1
,"2019-01-01" < "2019-01-02" as col2
,"2019-01-01" = "2019-01-02" as col3
,"2019-01-01" != "2019-01-02" as col4
*结果:0 1 0 1(0:错误;1:正确)