Kylin日期函数测试

4 篇文章 0 订阅
2 篇文章 0 订阅

目录

1、需求

2、函数调研

2.1 Date/time functions

2.2 Type conversion

3、测试

3.1 测试代码

3.2 测试结果

参考文章



1、需求

要在Kylin的sql中使用像date_sub类似的功能,date_sub的意义就是2018-08-08的前8天为2018-07-31。

2、函数调研

2.1 Date/time functions

OPERATOR SYNTAX

DESCRIPTION

LOCALTIME

Returns the current date and time in the session time zone in a value of datatype TIME

LOCALTIME(precision)

Returns the current date and time in the session time zone in a value of datatype TIME, with precision digits of precision

LOCALTIMESTAMP

Returns the current date and time in the session time zone in a value of datatype TIMESTAMP

LOCALTIMESTAMP(precision)

Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with precision digits of precision

CURRENT_TIME

Returns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE

CURRENT_DATE

Returns the current date in the session time zone, in a value of datatype DATE

CURRENT_TIMESTAMP

Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE

EXTRACT(timeUnit FROM datetime)

Extracts and returns the value of a specified datetime field from a datetime value expression

FLOOR(datetime TO timeUnit)

Rounds datetime down to timeUnit

CEIL(datetime TO timeUnit)

Rounds datetime up to timeUnit

YEAR(date)

Equivalent to EXTRACT(YEAR FROM date). Returns an integer.

QUARTER(date)

Equivalent to EXTRACT(QUARTER FROM date). Returns an integer between 1 and 4.

MONTH(date)

Equivalent to EXTRACT(MONTH FROM date). Returns an integer between 1 and 12.

WEEK(date)

Equivalent to EXTRACT(WEEK FROM date). Returns an integer between 1 and 53.

DAYOFYEAR(date)

Equivalent to EXTRACT(DOY FROM date). Returns an integer between 1 and 366.

DAYOFMONTH(date)

Equivalent to EXTRACT(DAY FROM date). Returns an integer between 1 and 31.

DAYOFWEEK(date)

Equivalent to EXTRACT(DOW FROM date). Returns an integer between 1 and 7.

HOUR(date)

Equivalent to EXTRACT(HOUR FROM date). Returns an integer between 0 and 23.

MINUTE(date)

Equivalent to EXTRACT(MINUTE FROM date). Returns an integer between 0 and 59.

SECOND(date)

Equivalent to EXTRACT(SECOND FROM date). Returns an integer between 0 and 59.

TIMESTAMPADD(timeUnit, integer, datetime)

Returns datetime with an interval of (signed) integer timeUnits added. Equivalent to datetime + INTERVAL 'integer' timeUnit

TIMESTAMPDIFF(timeUnit, datetime, datetime2)

Returns the (signed) number of timeUnitintervals between datetime and datetime2. Equivalent to (datetime2 - datetime) timeUnit

 

 

2.1.1 TIMESTAMPADD函数分析

TIMESTAMPADD(timeUnit, integer, datetime):

  • 第一个参数是timeUnit类型,理解为时间单位

  • 第二参数是整数类型,代表多少个timeUnit,比如3天、3小时

    timeUnit:

    MILLENNIUM | CENTURY | DECADE | YEAR | QUARTER | MONTH | WEEK | DOY | DOW | DAY | HOUR | MINUTE | SECOND | EPOCH

  • 第三个参数是datetime类型,目前了解到有DATE和TIMESTAMP两种。

 

2.2 Type conversion

OPERATOR SYNTAX

DESCRIPTION

CAST(value AS type)

Converts a value to a given type.

 

 

 

3、测试

3.1 测试代码

SELECT datekey,
       department_id,
       TIMESTAMPADD(DAY, -8, CAST('2018-08-08' AS DATE)) time_test1,
       TIMESTAMPADD(DAY, -8, CURRENT_TIMESTAMP) time_test2,  
       TIMESTAMPADD(DAY, -8, CURRENT_DATE) time_test3,
       (YEAR(TIMESTAMPADD(DAY, -8, CURRENT_DATE)) * 10000 + MONTH(TIMESTAMPADD(DAY, -8, CURRENT_DATE)) * 100 + DAYOFMONTH(TIMESTAMPADD(DAY, -8, CURRENT_DATE)) ) time_test4
  FROM app_hotel.app_cube_ptnr_poi_info_hos
 WHERE datekey=(YEAR(CURRENT_DATE) * 10000 + MONTH(CURRENT_DATE) * 100 + DAYOFMONTH(CURRENT_DATE) - 3)
 GROUP BY datekey,
          department_id;

 

 

3.2 测试结果

 

 

 

 

 

 

 

 

 

 

 

 

 

 

参考文章

calcite之SQL language

 

  • 1
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值