vertica系列:时间相关函数

-- *　注意: 本文的SQL是在　2017-09-14　测试的． 所以如果取当前日期, 结果为 2017-09-14*　

----------------------------
-- 相关数据类型
----------------------------
vertica 的 Date 类型仅包含日期, 而Oracle的Date的类不仅包含日期而且包含时间.
vertica 的 time, 不带日期, 仅包含小时分秒
vertica 的 timestamp 是完整的时间, 包含日期和时分秒
vertica 的 Datetime 类型是 timestamp 类型的同义词
interval 也是一种数据类型, 下面是几个示例:
interval '1:10:20' --时间间隔为1小时 10分 20秒
interval '5 year 1 month 1:10:20' --时间间隔为5年1一个月1个小时 10分 20秒
interval 中的时间单位包含year/month/day/hour/minute/second, 在表达式中既可以使用单数形式的英文, 也可以使用复数形式.
Interval 后面字符串如果是冒号的, 可以不用写单位, '1:2:3' 即是小时/分钟/秒.
Interval 后面字符串如果不是冒号的, 没有写单位, 即表示天数.
在Table中, 可以定义两种类型的interval字段, 分别是:
(1) INTERVAL DAY TO SECOND, 这个较为常用,精度是微秒,正负一亿多天的范围, 如果使用 literal的方式插值, 可以是几天几个小时几分几秒
(2) INTERVAL YEAR TO MONTH, 精度是到月,范围超级大, 如果使用 literal的方式插值, 可以是几年几个月, d但不能包含几天几个小时几分几秒

 

----------------------------
-- literal date/timestamp cast
----------------------------
select date '2014-07-25' --OK
select date('2014-07-25') --OK
select date('20140725') --OK
select date('2014/07/25') --OK
select date('now') -- 当天
select date('TODAY') -- 当天
select date('YESTERDAY') -- 昨天
select date('TOMORROW') -- 明天

----------------------------
-- 得到当前日期和时间戳
----------------------------
date('now') --result: 2017-09-14
date('TODAY') --result: 2017-09-14
select current_date --result: 2017-09-14
SELECT sysdate() --result: 2017-09-14 13:07:59
SELECT SYSDATE --result: 2017-09-14 13:07:59
select CURRENT_TIMESTAMP --result: 2017-09-14 13:07:59
select GETDATE() --返回当前时间戳, 注意是时间戳
select GETUTCDATE() --返回当前UTC的时间戳, 注意是时间戳
SELECT CURRENT_TIME --仅仅包含时间, 不包含日期

----------------------------
-- 数据类型转换和extract提取
----------------------------
select TO_DATE('20100101','YYYYMMDD')
select date(current_timestamp) -- timestamp 可以直接转成date类
select TO_TIMESTAMP('20100101 ','YYYYMMDD') -- 转成时间戳格式
SELECT TO_TIMESTAMP('20100101 23:59:59','YYYYMMDD HH24:MI:SS')
SELECT TO_TIMESTAMP('20100101 23:59:59:999 ','YYYYMMDD HH24:MI:SS:MS')
select date('now') + interval '0' -- date 转成 timestamp
select to_char(current_date ,'YYYYMMDD') -- date转字符串
select to_char(current_timestamp,'YYYYMMDD HH24:MI:SS') -- timestamp转字符串
select date(current_timestamp) -- timestamp 可以直接转成date类
select year(current_timestamp) --extract year
select month(current_timestamp) --extract month
SELECT day(current_timestamp) --extract day
select hour(CURRENT_TIME) --extract 小时值
select minute(CURRENT_TIME) --extract 分钟值
select week(sysdate); -- 当前在当年中的第几周
select quarter(sysdate); -- 当前是哪个季度
select date_part('doy',sysdate) -- 当天是当年的第几天, doy即 day of year
select date_part('dow',sysdate) -- 当天是当周中的第几天, dow 即 day of week
select date_part('month', sysdate) -- 当天的月份

 

----------------------------
-- 时间加减
-- date和timestamp都可以直接加数值, 也可以加 interval.
-- 加数值的话, 原来是什么类型结果就是什么类型.
-- 加一个interval, 其结果一定是 timestamp(即使原来是date类型)
----------------------------
select TIMESTAMPADD('DAY',-1,SYSDATE) -- 通用的数据加减函数
select date('TOMORROW') - date('today') -- 相差一天, 结果是:数值1
select date('now')+1 -- 结果为明天
select current_timestamp + interval '1' --结果是时间戳,明天的同一时间
select date('now') + interval '20 day' -- 加20天, 结果是时间戳
SELECT date('now') + interval '6:6:6' --结果是时间戳, 2017-09-14 06:06:06
select date('now') + interval '1' --结果是时间戳, 明天的零点
select date('now') + interval '0:1' --结果是时间戳, 明天的零点
select date('now') + interval '20 year' --结果是时间戳, 加20年
select current_timestamp + 1 --结果是时间戳,明天的同一时间
select current_timestamp + interval '1' --结果是时间戳,明天的同一时间
select date('now') + interval '0' -- date 转成 timestamp
SELECT ADD_MONTHS('20170131', 1) -- 加一个月, 结果为 2017-02-28
SELECT ADD_MONTHS('20171231', -1) -- 减一个月, 结果为 2017-11-30

----------------------------
-- 求时间间隔
----------------------------
SELECT TIMESTAMPDIFF(unit,A,B) -- B>A, 返回正值; B=A, 返回0; B<A,返回负值
select DATEDIFF(unit,A,B) -- B>A, 返回正值; B=A, 返回0; B<A,返回负值
select DATEDIFF('SECOND',sysdate+1,sysdate) -- (-86400)
select DATEDIFF('minute',sysdate,sysdate+1) -- 1440
select age_in_months(date('now'),date('now') + interval '1 year') --相差几个月, 结果是-12
select age_in_months(date('now'),date('now') + interval '1 year') --相差几个月, 结果是-12
select age_in_years(date('now') + interval '18 month' ,date('now') ) --相差几年,结果是1
select age_in_years(date('now') ,date('now') + interval '18 month') --相差几年,结果是-2

 

----------------------------
-- 几个关键词
----------------------------
select CURRENT_DATABASE --CURRENT_DATABASE 是关键词, 但也可当函数用 CURRENT_DATABASE()
select CURRENT_SCHEMA --CURRENT_SCHEMA 是关键词, 但也可当函数用 CURRENT_SCHEMA()
select CURRENT_USER --CURRENT_USER 是关键字, 但也可当函数用 CURRENT_USER()
select current_date --current_date 是关键字, 但可也当函数用 current_date()
SELECT CURRENT_TIME -- CURRENT_TIME 是关键字, 不同于其他CURRENT开头的关键词, 这个关键词不能当函数使用 , select CURRENT_TIME() 会报错, 返回值仅仅包含时间, 不包含日期
SELECT current_timestamp -- current_timestamp 是关键字, 不同于其他CURRENT开头的关键词, 这个关键词不能当函数使用 , select current_timestamp() 会报错

 

----------------------------
-- 时间格式化的指示字符串
----------------------------
Impala 和 Hive 等时间格式化的指示字符串写法和Java一样, 另外大小写是敏感的
Vertica 的时间格式化的指示字符串写法和Oracle一样, 大小写不铭感.
尤其是月份和分钟, mm在impala代表的是分钟, 在verica中代表的是月份, 不要混淆.

几个例子:
select to_timestamp('2018-05-16 12:19:37','yyyy-MM-dd')
-- Vertica的结果为 2018-05-16 00:00:00
-- impala的结果：2018-05-16 00:00:00

select to_timestamp('2018-05-16 12:19:37','yyyy-MM-dd HH:mm:ss')
-- Vertica的结果为 2019-07-19 12:00:37, !!错误!!
-- impala的结果： 2018-05-16 12:19:37

select to_timestamp('2018-05-16 12:19:37','yyyy-MM-dd HH:mi:ss')
-- Vertica的结果为 2018-05-16 12:19:37
-- impala执行会报错

select to_timestamp('2018-05-16 12:19:37','yyyy-MM-dd HH24:mi:ss')
-- Vertica的结果为 2018-05-16 12:19:37
-- impala执行会报错