Kylin日期函数测试

标签： kylin sql 日期函数

1、需求

2、函数调研

2.1 Date/time functions

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

本文链接：https://blog.csdn.net/qq_27657429/article/details/81508372

智能推荐

Reflect反射的基础知识

写个父类：写个子类：利用反射获得该子类中的属性，方法，构造，父类及接口：运行结果：...

spring cloud netflix (07) 服务的消费者(feign)

前言完整知识点：spring cloud netflix 系列技术栈 Feign (同步通信 HTTP通信) feign是基于接口完成服务与服务之间的通信的搭建Feign服务项目结构项目搭建 pom.xml application类 application.yml 使用feign完成服务与服务之间的通信 feign是基于接口完成服务与服务之间的通信的...

AtCoder Beginner Contest 174 E.Logs

AtCoder Beginner Contest 174 E.Logs 题目链接到最后才发现是二分，菜菜的我/(ㄒoㄒ)/~~ 我们直接二分 [1,max{a[i]}][1,max\lbrace a[i]\rbrace][1,max{a[i]}] 即可，对每一个 midmidmid，每个数 a[i]a[i]a[i] 只需要切 a[i]−1mid\frac{a[i]-1}{mid}mi...

小程序基础与实战案例

小程序开发工具与基础小程序开发准备：申请小程序账号（ appid ）下载并安装微信开发者工具具体步骤如下：先进入微信公众平台，下拉页面，把鼠标悬浮在小程序图标上然后点击小程序开发文档照着里面给的步骤，就可以申请到小程序账号了。然后就可以下载开发者工具了下载完打开后的界面就是这个样子下面让我们来新建一个小程序开发项目：在AppID输入自己刚刚注册的AppID就可以，或...

VMware centOS7 下通过minikube部署Kubernetes

1、环境准备： VMware CentOS-7-x86_64 CPU：2*2core 内存：8G 宿主机和虚拟机需网络互通，虚拟机外网访问正常 Centos发行版版本查看：cat /etc/centos-release root用户操作 2、禁用swap分区 Kubernetes 1.8开始要求关闭系统的Swap，可暂时关闭或永久禁用，使用 $ free -m 确认swap是否为开启状态 $ s...

Kylin日期函数测试

1、需求

2、函数调研

2.1 Date/time functions

2.2 Type conversion

3、测试

3.1 测试代码

3.2 测试结果

参考文章

智能推荐

猜你喜欢