vertica不是常用的数据库，在网上进行资料查询不是很容易找到，利用空余时间对一些常用到的命令进行归纳，希望对你有所帮助。

该文主要讲解vertica中常用到的时间处理。

系统当前时间的函数：CLOCK_TIMESTAMP，CURRENT_DATE，CURRENT_TIME [ ( precision ) ]，CURRENT_TIMESTAMP ( precision )，NOW()，SYSDATE()，GETDATE()，GETUTCDATE()，LOCALTIME [ ( precision ) ]，LOCALTIMESTAMP [ ( precision ) ]，TIMEOFDAY()

select CLOCK_TIMESTAMP();

返回值：2021-01-20 12:30:59   #对于一些数据库返回值可以到微秒，与系统设置有关

SELECT CURRENT_DATE;

返回值：2021-01-20 

select CURRENT_TIME;     
#该函数后面可以跟括号表示精度，如果指定的列标签不精确则必须省略括号。括号中的值为0到6之间的整数值，用于将秒分数字段结果四舍五入到指定的位数。

返回值：12:30:59

select CURRENT_TIMESTAMP;
#该函数后面可以跟括号表示精度，如果指定的列标签不精确则必须省略括号。括号中的值为0到6之间的整数值，用于将秒分数字段结果四舍五入到指定的位数。
#CURRENT_TIMESTAMP与CLOCK_TIMESTAMP返回值可能一致，但CLOCK_TIMESTAMP后面的括号不能省略。

返回值：2021-01-20 12:30:59

篇幅较大，部分函数不在进行举例说明，可自行验证。

ADD_MONTHS（开始日期，月数）

月份的加减，并返回date。开始日期为需要变更的时间，月份为增加或减少的数据，正数为增加月数，负数为减少月份。

#CURRENT_DATE为获取系统日期，格式是2020-01-01
#CURRENT_TIMESTAMP也是获取系统日期，格式是2020-01-01 12:12:12
#当前系统时间是2021-01-20
#当前系统日期添加1个月
select add_months(CURRENT_DATE,1) ; #1是增加月份

返回值：2021-02-20

#当前系统日期减1个月
select add_months(CURRENT_DATE,-1) ; #-1是减少月份

返回值：2020-12-20

#2月份比较特殊，当遇到闰月时是29天，"Leap Month"是对数据的说明，但是经过验证发现，这个后缀带与不带都不影响结果数据
SELECT ADD_MONTHS('31-Jan-2020'::TIMESTAMP, 1) "Leap Month"

返回值：2020-02-29

#以下示例将时区设置为EST；然后它将24个月添加到指定PST时区的TIMESTAMPTZ中，这个格式在工作中不常见
SELECT ADD_MONTHS('2008-02-29 23:30 PST'::TIMESTAMPTZ,24)

返回值：2010-03-01

AGE_IN_MONTHS([ date1,] date2)

求两个时间差，返回月份，date1大于date2时为正值，反之则为负值。

#获取月份差
SELECT AGE_IN_MONTHS('2020-06-21'::TIMESTAMP,'1972-03-02'::TIMESTAMP);

返回值：579

#获取与当前时间的月份差，当前时间：2021-01-20
SELECT AGE_IN_MONTHS ('1949-10-01'::DATE);

返回值：855

AGE_IN_YEARS( [ date1,] date2 ) 

获取两个日期的年数差，返回值为整数，当date1大于date2时为正值，反之则为负值。为降低篇幅这里不在进行举例说明，用法与AGE_IN_MONTHS函数相同。

DATE ( value )

该函数是把输入值转换为日期类型，较为常用。

select date(1)

返回值：0001-01-01

select date(756382)

返回值：2071-11-26

select date('today')

返回值：2021-01-20

DATEDIFF ( datepart, start, end )

计算两个时间差，在计算中排除开始日期。

datepart指定返回的日期或时间间隔类型。如果datepart是一个表达式，则必须将其括在括号中。

datepart必须计算为以下字符串文字之一（带引号或不带引号）：

  • year| yy|yyyy
  • quarter| qq|q
  • month| mm|m
  • day| dayofyear| dd| d| dy|y
  • week| wk|ww
  • hour | hh
  • minute| mi|n
  • second| ss|s
  • millisecond | ms
  • microsecond| mcs|us
SELECT DATEDIFF(day, INTERVAL '26 days', INTERVAL '1 month ');

返回值：4

SELECT DATEDIFF(year, '01-01-2005'::date, '12-31-2008'::date);

返回值：3

SELECT DATEDIFF(month, '01-31-2005'::date, '09-30-2005'::date);

返回值：8

日期月中第几天：DAY ( value )，DAYOFMONTH ( date )

SELECT DAY(TIMESTAMP '2021-01-20 12:34');

返回值：20

SELECT DAYOFMONTH(TIMESTAMP '2021-01-20 12:34');

返回值：20

日期在一星期中的第几天：DAYOFWEEK ( date )，DAYOFWEEK_ISO ( date )

SELECT DAYOFWEEK (TIMESTAMP '2021-01-20 12:34');
#国外以星期天作为一个星期的第一天，这个在使用的时候需要主意。2021-01-20为星期三

返回值：4 

SELECT DAYOFWEEK_ISO(TIMESTAMP '2021-01-20 12:34');

返回值：3

DAYOFYEAR ( date )

获取输入日期是一年中的第几天，返回值为整数。

SELECT DAYOFYEAR(TIMESTAMP '2021-02-20 12:34');

返回值：51

DAYS ( date )

日期转换为数值形。公元前为负值，公元后为正值。

SELECT DAYS (DATE '2021-01-20');

返回值：737810

SELECT DAYS (DATE '0034-01-22 BC');

返回值：-12397

HOUR( date )

获取输入日期的小时部分数据，同day()函数。

LAST_DAY ( date )

获取输入日期月份中的最后一天。

SELECT LAST_DAY('2016-02-28 23:30');

返回值：2016-02-29

MICROSECOND ( date )

获取输入日期的微秒部分数据，同HOUR( date )函数。

MIDNIGHT_SECONDS ( date )

获取午夜到日期时间部分之间的秒数。

SELECT MIDNIGHT_SECONDS('2021-01-20 12:00'::TIMESTAMP);

返回值：43200

MINUTE ( date )

获取输入时间数据中的分钟部分，同HOUR( date )函数。

SECOND ( date )

获取输入时间数据中的秒部分，同HOUR( date )函数。

QUARTER ( date )

获取输入日期的季度，返回值为整数。同HOUR( date )函数。

MONTH ( date )

获取输入时间数据中的月数部分，同HOUR( date )函数。

YEAR( date )

获取输入时间数据中的年数据部分，同HOUR( date )函数。

YEAR_ISO ( date )

指定日期的年份部分的整数。返回值基于ISO 8061标准。

SELECT YEAR_ISO('2021-01-04'::DATE);

返回值：2021

SELECT YEAR_ISO('2021-01-03'::DATE);

返回值：2020

注：注意第二个返回值是2020

WEEK ( date )

以整数返回指定日期的一年中的星期，其中第一周从1月1日或之前的第一个星期日开始。

SELECT WEEK ('2021-01-02'::DATE);

返回值：1

SELECT WEEK ('2021-01-03'::DATE);

返回值：2

注：第二个结果中出现2，是因为国外以星期日作为一周的第一天。国内不适用，在使用中要注意。

WEEK_ISO ( date )

以整数形式返回指定日期的一年中的星期，其中第一周从星期一开始并包含1月4日。此函数符合ISO 8061标准。

SELECT WEEK_ISO('2021-01-04'::DATE);

返回值：1

SELECT WEEK_ISO('2021-01-03'::DATE);

返回值：53

注：第二个结果是53的主要原因是该部分说的该时间是2020年的第53周，在使用过程中要特别注意。

MONTHS_BETWEEN ( date1 , date2 )

获取两个时间之间的月份，返回整数或者FLOAT，当date1小于date2为负值，反之为正值。

SELECT MONTHS_BETWEEN ('2021-01-20'::TIMESTAMP, '2021-02-25'::TIMESTAMP);

返回值：-1.1612903226

NEXT_DAY( 'date', 'day-string')

获取输入日期在之后一周中特定日期的第一个实例的日期。date为输入日期，day-string为星期的英文，可全拼可缩写，且不区分大小写，例如Monday或者mon。

select next_day('2021-01-20','mon');

返回值：2021-01-25

OVERLAPS

获取两个时间段是否有交集，返回BOOLEAN类型的值。格式为( start, end ) OVERLAPS ( start, end )或者( start, interval) OVERLAPS ( start, interval )

其中start表示开始时间，end表示结束时间，interval表示间隔。

SELECT (DATE '2016-02-16', DATE '2016-12-21') OVERLAPS (DATE '2008-10-30', DATE '2016-10-30');

返回值：true

SELECT (DATE '2016-02-16', INTERVAL '1 week') OVERLAPS (DATE '2016-10-16', INTERVAL '-8 months');

返回值：true

STATEMENT_TIMESTAMP()/TRANSACTION_TIMESTAMP()

获取事务开始执行时的系统时间，在事务处理期间的多个调用返回相同的时间戳。

=> SELECT foo, bar FROM (SELECT STATEMENT_TIMESTAMP() AS foo)foo, (SELECT STATEMENT_TIMESTAMP() as bar)bar;
                          foo | bar
------------------------------+-------------------------------
2016-12-07 14:55:51.543988-05 | 2016-12-07 14:55:51.543988-05
(1 row)

TIMESTAMPADD ( datepart, count, start-date )

获取增加天数或者月数等后的时间。datepart类型同上文，count为数值，start-date为输入日期。

SELECT TIMESTAMPADD (DD, 10,'2021-01-20 12:30:30');

返回值：2021-01-30 12:30:30

TIMESTAMPDIFF ( datepart, start, end )

获取时间差。datepart同上文，start为输入开始时间，end为输入结束时间。

SELECT TIMESTAMPDIFF (YEAR,'1-1-2006 12:34:00', '1-1-2008 12:34:00');

返回值：2

ROUND( rounding-target[, 'precision'] )/TIMESTAMP_ROUND ( rounding-target[, 'precision'] )

四舍五入指定的日期或时间。

SELECT ROUND('9-22-2011 12:34:00'::TIMESTAMP, 'MM');

返回值：2011-10-01 00:00:00

TIMESTAMP_TRUNC( trunc-target[, 'precision'] )/TRUNC( trunc-target[, 'precision'] )

截断输入时间。

SELECT TIMESTAMP_TRUNC('9-22-2011 12:34:00'::TIMESTAMP, 'MM');

返回值：2011-09-01 00:00:00

ISFINITE ( timestamp )

测试是否为数据样式数据，返回BOOLEAN类型的值。

JULIAN_DAY ( date )

根据儒略历返回指定日期的整数值，其中第1天是儒略时期的第一天，公元前4713年1月（公历，公元前4714年11月24日）

NEW_TIME( 'timestamp' , 'timezone1' , 'timezone2')

时区转换函数，timestamp为输入时间，timezone1\timezone2为时区。

  • GMT： 格林威治标准时间
  • AST/ ADT：大西洋标准/夏令时间
  • EST/ EDT：东部标准时间/夏令时间
  • CST/ CDT：中央标准时间/夏令时
  • MST/ MDT：山区标准/夏令时间
  • PST/ PDT：太平洋标准时间/夏令时间
SELECT NEW_TIME('2021-01-20 01:00:00', 'EST', 'PST');

返回值：2021-01-19 22:00:00
 

DATE_PART ( 'field', date )

从输入时间中提取需求字段。filed的值包含：DAY、DECADE、DOQ、DOW、DOY、EPOCH、HOUR、ISODOW、ISOWEEK、ISOYEAR、MICROSECONDS、MILLENNIUM、MILLISECONDS、MINUTE、MONTH、QUARTER、SECOND、TIME ZONE、TIMEZONE_HOUR、TIMEZONE_MINUTE、WEEK、YEAR。

DATE_TRUNC( precision, trunc-target )

将日期和时间值截断为指定的精度。返回值是与输入值相同的数据类型。所有小于指定精度的字段都设置为0，或者将日期和月份设置为1。，使用方式与DATE_PART 函数相似。

EXTRACT ( field FROM date )

提取输入日期在上一个单位的第多少天或月或年等

TIME_SLICE( expression, slice-length [, 'time--unit' [, 'start-or-end' ] ] )

按不同的固定时间间隔聚合数据，并将上舍入后的输入TIMESTAMP值返回为与时间片间隔的开始或结束相对应的值。

给定一个输入TIMESTAMP值，例如2000-10-28 00:00:01，3秒时间片间隔的开始时间为2000-10-28 00:00:00，而同一时间片的结束时间为2000-10-28 00:00:03。

附：1、该文主要参考文献《Vertica_10.0.x_Complete_Documentation》

       2、由于部分字段不常用，未进行详细说明。如有需要可以点击链接https://www.vertica.com/查看详细文献。

 

# 数据库
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

6个实例带你解读TinyVue 组件库跨框架技术

avatar 华为云开发者联盟
cover

华为云FunctionGraph构建高可用系统的实践

avatar 华为云开发者联盟
cover

11个Python循环技巧

avatar 华为云开发者联盟