我们常用的函数有date_add、date_sub、current_date、date_format、next_date、datediff、year、month、day、hour、minute、second、last_day,一般我们都是通过利用这些函数来获取某一天的时间、
属于第几周、第几个月、第几个季度、星期几、最近七天等等。
常用函数的基本用法
1、current_date() 返回查询评估开始的当前日期;
select current_date(); //2020-07-15
2、date_add(date,num) 返回date之后的num日期,num可正可负(date_sub用来计算某个日期前n天的日期,这里记住date_add()就可以了)
select date_add('2020-07-15',-1); //2020-07-14
select date_add('2020-07-15',1); //2020-07-16
3、datediff 返回两个日期之间的天数
select datediff('2020-07-23',''2020-07-05') //8
4、day、month、year分别返回某个日期的日、月、年,day函数也可用dayofmonth,他们只是叫法不同而已。
5、dayofweek返回某个日期是星期几
select dayofweek('2020-07-15')
这里要注意:返回值1=星期日,2=星期一…
6、from_unixtime(unix_time,format) 以指定的格式返回unix_time
select from_unixtime(0,'yyyy-MM-dd HH:mm:ss') //1970-01-01 00:00:00
7、date_format(date/timestamp/string,format) 返回指定的时间格式
在使用的时候要特别注意原字段的类型,要是报错,可以用cast()强转一下
8、last_day 返回这个月的最后一天
9、next_day(start_day,day_of_week) 返回晚于start_day并按指示命名的第一个日期
select next_day('2020-06-24','tu')
10、unix_timestamp 将时间转化为1970-01-01以来的秒数
select unix_timestamp('2020-07-14')
11、weekofyear 返回给定日期一年蛋黄粽的一周,
常用到的一些函数搭配使用
1、求本周的第一天和最后一天
说明:本文以下 day 为 ‘2020-07-16’
select date_add(day,1-dayofweek(day)) //2020-07-12 --星期日
select date_add(day,7-dayofweek(day)) //2020-07-12 --星期六
select date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) = 1 end) --周一
select date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) = 1 end) --周日
2、求当天属于当年第几个季度
select lpad(ceil(month(day)),2,0)
ps:lpad(str,len,pad) 返回str,左边用pad填充长度为len
如果str长于len,则返回值缩短为len个字符
如果是空填充字符串则返回值为null
select lpad('hel',5,'') //空值
3、本周第几天
date_format('2020-07-14','u') --第一种方式
7 - datediff(next_day('2020-07-14','sun'),'2020-07-14') --第二种方式
4、这个月的第几周
select weekofyear(day) - weekofyear(trunc(day),'MM') +1
5、这个月第一天,今年一天
select trunc(day,'MM')
select trunc(day,'YY')
6、这月的最后一天,今年最后一天
select last_day(add_months(trunc(day,'YY'),12))
select last_day(day)
7、这个季度第一天、最后一天
to_date(concat(year(day),'-',lpad(ceil(month(day)/3)*3 -2,2,0),'-01'))
last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3)*3 -2,2,0),'-01')))
欢迎大家补充~