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' --OKselect date('2014-07-25') --OKselect date('20140725') --OKselect date('2014/07/25') --OKselect date('now') -- 当天select date('TODAY') -- 当天

select date('YESTERDAY') -- 昨天select date('TOMORROW') -- 明天

------------------------------ 得到当前⽇期和时间戳----------------------------date('now') --result: 2017-09-14date('TODAY') --result: 2017-09-14select current_date --result: 2017-09-14

SELECT sysdate() --result: 2017-09-14 13:07:59SELECT 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 yearselect month(current_timestamp) --extract monthSELECT day(current_timestamp) --extract dayselect 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 weekselect date_part('month', sysdate) -- 当天的⽉份

------------------------------ 时间加减

-- date和timestamp都可以直接加数值, 也可以加 interval. -- 加数值的话, 原来是什么类型结果就是什么类型.

-- 加⼀个interval, 其结果⼀定是 timestamp(即使原来是date类型)----------------------------select TIMESTAMPADD('DAY',-1,SYSDATE) -- 通⽤的数据加减函数select date('TOMORROW') - date('today') -- 相差⼀天, 结果是:数值1select 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:06select 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-28SELECT ADD_MONTHS('20171231', -1) -- 减⼀个⽉, 结果为 2017-11-30

------------------------------ 求时间间隔

----------------------------SELECT TIMESTAMPDIFF(unit,A,B) -- B>A, 返回正值; B=A, 返回0; BA, 返回正值; B=A, 返回0; Bselect 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') ) --相差⼏年,结果是1select 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开头的关键词, 这个关键词不能当函数使⽤ , selectCURRENT_TIME() 会报错, 返回值仅仅包含时间, 不包含⽇期

SELECT current_timestamp -- current_timestamp 是关键字, 不同于其他CURRENT开头的关键词, 这个关键词不能当函数使⽤ , selectcurrent_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执⾏会报错