— * 注意: 本文的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执行会报错