sql中的时间表示

hive/spark

1.时间表示
select current_date -- 当前时间(日)
select unix_timestamp() -- 当前时间戳
select from_unixtime(unix_timestamp(),'yyy-MM-dd HH:mm:ss') -- 时间戳转日期

2.常用函数
select date_sub(current_date,3) -- 当前日期-3
select date_add(current_date,3) -- 当前日期+3
select year(current_date)
select month(current_date)
select quarter(current_date) -- 季度
select weekofyear(current_date) -- 日期所在年的第几周
select day(current_date)
select minute('2020-12-10 12:19:01')
select second('2020-12-10 12:19:01')
select next_day(current_date,'MO') -- 当前日期的下一个周一
select next_day(current_date,'tue') -- 当前日期的下一个周二
select next_day(current_date,'wed') -- 当前日期的下一个周三
select datediff('2020-12-17','2020-12-10') -- 日期差(天数)

3.周/月

绩效月：上个月20号到本月19号
select concat(substr(date_sub(current_date,day(current_date)+1), 1,7), '-20')
select concat(substr(current_date, 1,7),'-19')
自然周：
select concat(date_sub(next_day(current_date,'MO'),7),'~',date_sub(next_day(current_date,'MO'),1))
自然月：
select concat(add_months(trunc(current_date,'MM'),-1),'~',add_months(trunc(current_date,'MM'),0))

4.trunc函数：(数字/日期截取)，hive中仅支持日期截取，所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY

select trunc('2020-04-12 20:01:00','YYYY') -- 2020-01-01
select trunc('2020-04-12 20:01:00','MM) -- 2020-04-01

5.pmod函数：取余函数,第二个参数需设定为星期一，第一个参数才能正确展示为本周的星期几

select pmod(datediff('2020-12-16','2020-12-07'),7)+1 -- 2020-12-16 是星期三
select pmod(datediff('2020-12-16','2020-12-07')+1,7) -- 2020-12-16 是星期三

6.时间段表示：

response_time 格式为 yyyy-MM-dd HH:mm:ss

case when hour(response_time) between 0 and 8 then concat('0', hour(response_time),'~','0', hour(response_time)+1)
when hour(response_time)=9 then concat('0', hour(response_time),'~', hour(response_time)+1)
else concat(hour(response_time),'~',hour(response_time)+1)
end

presto

1.常用函数

select current_timezone() -- 当前时区
select current_date -- 当前日期
select current_time -- 当前时间加时区
select now()
select localtime -- 当前查询时间
select current_timestamp -- 带时区的时间戳
select localtimestamp -- 不带时区的时间戳
select typeof(localtimestamp)
select to_unixtime(current_timestamp) -- 当前unix时间戳
select parse_duration('28800s') -- 任意数字转换成hh:mm:ss
select date_parse('2020-12-17 20:00:00','%Y-%m-%d %H:%i:%s')
select typeof(date_parse('2020-12-17 20:00:00','%Y-%m-%d %H:%i:%s')) -- 字符串解析为timstamp ??? 什么时候用yyyy-MM-dd HH:mm:ss ???只写到日期为何不能自动以0补足
select typeof(date_format(timestamp '2020-12-17 20:00:00','%Y-%m-%d %H:%i:%s')) -- 指定格式的时间戳转成字符串
select to_milliseconds(interval '8' hour) -- 28800000 距离当天零时过去的毫秒数

/*** to_date()函数要求小时在0~11内,超过此范围会提示:Value 12 for hourOfHalfday must be in the range [0,11] ***/
select to_date('2019-07-19 13:01:01','yyyy-mm-dd hh:mi:ss') -- error
select to_date('2011-12-08 12:01:01','yyyy-mm-dd hh:mi:ss')
select to_date('2011-12-08 13:01:51','yyyy-mm-dd hh24:mi:ss') -- 小时改成24小时制即可
select to_unixtime(timestamp'2016-06-27 21:00:00') --日期转时间戳
自然周:
select concat(cast(date_add('day',-7,date_trunc('week',current_date)) as varchar),'~',cast(date_add('day',0,date_trunc('week',current_date)) as varchar))
自然月:
select concat(cast(date_add('month',-1,date_trunc('month',current_date)) as varchar),'~',cast(date_add('month',0,date_trunc('month',current_date))as varchar))

2.日期时间运算

  select timestamp '2012-08-08 10:00:00' + interval '2' day
  select date '2019-07-01'+interval '2' year
  select date '2012-08-08' + interval '2' MONTH

运算符	示例	结果
+	date '2012-08-08' + interval '2' day	2012-08-10
+	time '01:00' + interval '3' hour	04:00:00
+	timestamp '2012-08-08 01:00' + interval '29' hour	2012-08-09 06:00:00.000
+	timestamp '2012-10-31 01:00' + interval '1' month	2012-11-30 01:00:00.000
+	interval '2' day + interval '3' hour	2 03:00:00.000
+	interval '3' year + interval '5' month	3-5
-	date '2012-08-08' - interval '2' day	2012-08-06
-	time '01:00' - interval '3' hour	22:00:00.000
-	timestamp '2012-08-08 01:00' - interval '29' hour	2012-08-06 20:00:00.000
-	timestamp '2012-10-31 01:00' - interval '1' month	2012-09-30 01:00:00.000
-	interval '2' day - interval '3' hour	1 21:00:00.000
-	interval '3' year - interval '5' month	2-7

3.截取函数date_trunc(unit,x)——>[same as input]

  栗子：date_trunc(unit,2001-08-22 03:04:05.321)

单位unit	结果
second	2001-08-22 03:04:05.000
minute	2001-08-22 03:04:00.000
hour	2001-08-22 03:00:00.000
day	2001-08-22 00:00:00.000
week	2001-08-20 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

4.间隔函数

  date_add(unit,value,timestamp) —> [same as input]
  date_diff(unit_timestamp1,timestamp2) —> bigint 两个时间间隔

	支持的类型
unit	millisecond、second、minute、hour、day、week、month、quarter、year

5.持续时间函数

  parse_duration(string) —> interval

  select parse_duration('28800s') -- 0 08:00:00.000
  select parse_duration('3.5d') --3 12:00:00.000
  select parse_duration('2.5m') --0 00:02:30.000

unit	解释
ns	纳秒
us	微秒
ms	毫秒
s	秒
m	分
h	时
d	天

6.抽取函数

  select extract(field from x)
  select extract(year from current_date)

field	描述
year	year()
quarter	quarter()
month	month()
week	week()
day、day_of_month	day()
day_of_week、dow	day_of_week()
day_of_year、doy	day_of_year()
year_of_week、yow	year_of_week()
hour	hour()
minute	minute()
second	second()
timezone_hour	timezone_hour()
timezone_minute	timezone_minute()