SQL：日期函数及常见的日期换算

由于工作主要涉及hive和MySQL，两个平台大部分函数在名称和功能上都是相同的 ，个别有差异，根据需要自去验证。

1、截取日期的部分

　　second(date)：返回日期中的秒
　　minute(date)：返回日期中的分钟
　　hour(date)：返回日期中的小时
　　day(date)：返回日期中的天
　　week(date)：返回日期在当前周数
　　month(date)：返回日期中的月份
　　year(date)：返回日期中的年

　　day(date)：day('2021-01-10')，返回日期的‘日’部分值。

select  day('2021-01-10') d;　　　　输出结果：==> 10

 　　hive：extract(field FROM source) 从从源中检索诸如天数或小时之类的字段（从配置单元2.2.0开始）。源必须是日期、时间戳、间隔或可以转换为日期或时间戳的字符串。支持的字段包括：日、周日、小时、分钟、月、季度、秒、周和年。

select extract(month from "2016-10-20") results in 10.

select extract(hour from "2016-10-20 05:06:07") results in 5.

select extract(dayofweek from "2016-10-20 05:06:07") results in 5.

select extract(month from interval '1-3' year to month) results in 3.

select extract(minute from interval '3 12:20:30' day to second) results in 20.

 

2、current_date()/curdate() ： 与now()函数一样，返回当前日期，与now()函数不同的是current_date()返回的是string，now()返回的是timestamp格式

select  current_date() cc,curdate() c,now() n 

 

3、date_add/date_sub(date,n)：这是hive平台的模式，在给定的日期加/减 n天，这是hive平台的模式，只作日期的加减。 

select date_add('2022-10-10',7) 　　　　　　输出：2022-10-17

 

4、date_add/date_sub(date,interval expr type)：这是MySQL平台的模式，根据type类型，在给定的日期上加/减一个时间单位

select date_sub('2022-10-10',INTERVAL 10 day ) sub_day, 　　　　　　输出：2022-09-30
date_sub('2022-10-10',INTERVAL 12 month ) sub_month, 　　　　　　输出：2021-10-10
date_sub('2022-10-10',INTERVAL 3 year ) sub_year　　　　　　输出：2019-10-10

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

 

5、datediff(date1,date2)：date1-date2，返回两个日期间的天数差；个别系统这个函数有第三个参数，更改第三个参数值可以得到两个时间的年、月、日、时、分、秒等差值。

select  datediff('2021-01-01','2022-01-01') days; 　　　　输出结果：==> -365

 

 6、last_day(date)：hive平台函数，返回给定日期所在月份的最后月末日期

select last_day('2022-10-10')            输出：2022-10-31

 

7、trunc(date,fmt)：根据fmt截取日期，以下为trunc常用的几种情形

select trunc(date,'YEAR') 　　　　　　 返回日期所在年的第一天　　
select trunc(date, 'MM') 　　　　　　返回日期所在月份的第一天
select trunc(date, 'Q') 　　　　　　返回日期所在季度的第一天 
select trunc(date,'d') 　　　　　　 返回日期所在星期的第一天(星期天)
select trunc(date, 'hh') 　　　　　　返回当前时间小时的开始，如当前时间为2022-10-10 16:47:23 返回2012-10-10 16:00:00  

 

8、date_sub + trunc + add_months + last_day：以指定日期为参考点，返回特定的月初、月末，自定义报表统计中常用。

select trunc(add_months(current_date,-1),'MM')  上月初
select trunc(add_months(current_date,1),'MM')  下月初

select date_sub(trunc(current_date,'MM'),1)  上月末
select last_daty(add_months(current_date,-1))  上月末
select last_daty(add_months(current_date,1)) 下月末

 

9、unix_timestamp()：返回当前时间戳，或把给定的日期转换成时间戳

select unix_timestamp() tm1;  -- 当前时间戳 
select (unix_timestamp('2022-10-10 12:03:55') - unix_timestamp('2022-10-10 11:03:55')) tm2; -- 秒差 
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/60 tm3; -- 分钟差 
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/3600 tm4; -- 小时差 

 

10、next_day(date,fmt)：返回给定日期对应的下个星期的指定星期几，由于一般运营工作中周会一般很少在周一，大多会是每周周二、周四，因此周报一般也不是正常的周一到周日，而是上周二到本周一、上周四到本周三之类，因此，这个函数在周报统计就显得非常实用了，fmt参数： MO 周一；TU 周二；WE 周三；TH 周四；FR 周五；SA 周六；SU周日。

select next_day(current_date(),'MO'); -- 下周一
select next_day(current_date,'MO'); -- 下周末
select date_sub(next_day(current_date(),'MO'),7) ; -- 本周一
select date_sub(next_day(current_date,'MO'),1); -- 本周末

 

12、pmod + datediff：返回给定日期的是星期几

select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于0是当前天为星期日
select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于1是当前天为星期1
select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于2是当前天为星期2
select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于3是当前天为星期3
select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于4是当前天为星期4
select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于5是当前天为星期5
select pmod(datediff(current_date, '2012-01-01'), 7) ss;  -- 当等于6是当前天为星期6  

 

13、weekday(date)：返回给定日期是星期几，0 周一，1 周二，2 周三，3 周四，4 周五，5 周六，6 周日

 

14、from_unixtime ： 转化unix时间戳到当前时区的时间格式，from_unixtime +unix_timestamp Hive中yyyymmdd和yyyy-mm-dd日期之间的切换

select from_unixtime(1665374635,’yyyyMMdd’); -- 输出：20221010
select from_unixtime(unix_timestamp('20221010','yyyymmdd'),'yyyy-mm-dd') ;  -- 20221010转成2022-10-10
select from_unixtime(unix_timestamp('2022-10-10','yyyy-mm-dd'),'yyyymmdd');  -- 2022-10-10转成2022-10-10

 

date_format(date,format)：格式日期

select  date_format(NOW(),'%b %d %Y %h:%i %p') df1, 　　　　　　输出结果：==> Oct 12 2022 09:59 PM
date_format(NOW(),'%m-%d-%Y') df2,　　　　　　输出结果：==>  10-12-2022
date_format(NOW(),'%d %b %y') df3,　　　　　　输出结果：==>  12 Oct 22
date_format(NOW(),'%d %b %Y %T:%f') df4; 　　　　　　输出结果：==>  12 Oct 2022 21:59:58:000000

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位