目录
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 测试结果
参考文章