Flink基础（58）：FLINK-SQL函数(21)内置函数（16）日期函数（三）

•  WEEK

语法

BIGINT WEEK(DATE date)
BIGINT WEEK(TIMESTAMP timestamp)

入参

参数	数据类型
date	DATE
timestamp	TIMESTAMP

功能描述

计算指定日期在一年中的第几周，周数取值区间1~53。

示例

• 测试数据

  dateStr(VARCHAR)	date1(DATE)	ts1(TIMESTAMP)
  2017-09-15	2017-11-10	2017-10-15 00:00:00

• 测试语句
   

  SELECT WEEK(TIMESTAMP '2017-09-15 00:00:00') as int1,
   WEEK(date1) as int2,
   WEEK(ts1) as int3,
   WEEK(CAST(dateStr AS DATE)) as int4
  FROM T1;

• 测试结果

  int1(BIGINT)	int2(BIGINT)	int3(BIGINT)	int4(BIGINT)
  37	45	41	37

•  YEAR

语法

BIGINT YEAR(TIMESTAMP timestamp)
BIGINT YEAR(DATE date)

入参

参数	数据类型
date	DATE
timestamp	TIMESTAMP

功能描述

返回输入时间的年份。

示例

• 测试数据

  tsStr(VARCHAR)	dateStr(VARCHAR)	tdate(DATE)	ts(TIMESTAMP)
  2017-10-15 00:00:00	2017-09-15	2017-11-10	2017-10-15 00:00:00

• 测试语句
   

  SELECT YEAR(TIMESTAMP '2016-09-15 00:00:00') as int1,
   YEAR(DATE '2017-09-22') as int2,
   YEAR(tdate) as int3,
   YEAR(ts) as int4,
   YEAR(CAST(dateStr AS DATE)) as int5,
   YEAR(CAST(tsStr AS TIMESTAMP)) as int6
  FROM T1;          

• 测试结果

  int1(BIGINT)	int2(BIGINT)	int3(BIGINT)	int4(BIGINT)	int5(BIGINT)	int6(BIGINT)
  2016	2017	2017	2017	2015	2017

•  MONTH

语法

BIGINT MONTH(TIMESTAMP timestamp) 
BIGINT MONTH(DATE date)

入参

参数	数据类型
time	TIME
timestamp	TIMESTAMP

功能描述

返回输入时间参数中的月，范围1～12。

示例

• 测试数据

  a(TIMESTAMP)	b(DATE)
  2016-09-15 00:00:00	2017-10-15

• 测试语句
   

  SELECT
   MONTH(cast( a as TIMESTAMP)) as int1,
   MONTH(cast( b as DATE)) as int2
  FROM T1;     

• 测试结果

  int1(BIGINT)	int2(BIGINT)
  9	10

 

•  HOUR

语法

BIGINT HOUR(TIME time)
BIGINT HOUR(TIMESTAMP timestamp)    

入参

参数	数据类型
time	TIME
timestamp	TIMESTAMP

功能描述

返回输入时间参数time或timestamp中的24小时制的小时数，范围0～23。

示例

• 测试数据

  datetime1(VARCHAR)	time1(VARCHAR)	time2(TIME)	timestamp1(TIMESTAMP)
  2017-10-15 11:12:13	22:23:24	22:23:24	2017-10-15 11:12:13

• 测试语句
   

  SELECT HOUR(TIMESTAMP '2016-09-20 23:33:33') AS int1,
   HOUR(TIME '23:30:33') AS int2,
   HOUR(time2) AS int3,
   HOUR(timestamp1) AS int4,
   HOUR(CAST(time1 AS TIME)) AS int5,
   HOUR(TO_TIMESTAMP(datetime1)) AS int6
  FROM T1;              

• 测试结果

  int1(BIGINT)	int2(BIGINT)	int3(BIGINT)	int4(BIGINT)	int5(BIGINT)	int6(BIGINT)
  23	23	22	11	22	11

•  DAYOFMONTH

语法

BIGINT DAYOFMONTH(TIMESTAMP time)
BIGINT DAYOFMONTH(DATE date)

入参

参数	数据类型
date	DATE
time	TIMESTAMP

功能描述

返回输入时间参数date或time中所指代的“日”。返回值范围为1～31。

示例

• 测试数据

  tsStr(VARCHAR)	dateStr(VARCHAR)	tdate(DATE)	ts(TIMESTAMP)
  2017-10-15 00:00:00	2017-09-15	2017-11-10	2017-10-15 00:00:00

• 测试语句
   

  SELECT DAYOFMONTH(TIMESTAMP '2016-09-15 00:00:00') as int1,
   DAYOFMONTH(DATE '2017-09-22') as int2,
   DAYOFMONTH(tdate) as int3,
   DAYOFMONTH(ts) as int4,
   DAYOFMONTH(CAST(dateStr AS DATE)) as int5,
   DAYOFMONTH(CAST(tsStr AS TIMESTAMP)) as int6
  FROM T1; 

• 测试结果

  int1(BIGINT)	int2(BIGINT)	int3(BIGINT)	int4(BIGINT)	int5(BIGINT)	int6(BIGINT)
  15	22	10	15	15	15

•  MINUTE

语法

BIGINT MINUTE(TIME time) 
BIGINT MINUTE(TIMESTAMP timestamp)

入参

参数	数据类型
time	TIME
timestamp	TIMESTAMP

功能描述

返回输入时间参数中time或timestamp中的“分钟”部分。取值范围0～59。

示例

• 测试数据

  datetime1(VARCHAR)	time1(VARCHAR)	time2(TIME)	timestamp1(TIMESTAMP)
  2017-10-15 11:12:13	22:23:24	22:23:24	2017-10-15 11:12:13

• 测试语句
   

  SELECT MINUTE(TIMESTAMP '2016-09-20 23:33:33') as int1,
   MINUTE(TIME '23:30:33') as int2,
   MINUTE(time2) as int3,
   MINUTE(timestamp1) as int4,
   MINUTE(CAST(time1 AS TIME)) as int5,
   MINUTE(CAST(datetime1 AS TIMESTAMP)) as int6
  FROM T1;

• 测试结果

  int1(BIGINT)	int2(BIGINT)	int3(BIGINT)	int4(BIGINT)	int5(BIGINT)	int6(BIGINT)
  33	30	23	12	23	12

•  SECOND

语法

BIGINT SECOND(TIMESTAMP timestamp)
 BIGINT SECOND(TIME time)    

入参

参数	数据类型
time	TIME
timestamp	TIMESTAMP

功能描述

返回输入时间参数中的“秒”部分，范围0～59。

示例

• 测试数据

  datetime1(VARCHAR)	time1(VARCHAR)	time2(TIME)	timestamp1(TIMESTAMP)
  2017-10-15 11:12:13	22:23:24	22:23:24	2017-10-15 11:12:13

• 测试语句
   

  SELECT SECOND(TIMESTAMP '2016-09-20 23:33:33') as int1,
   SECOND(TIME '23:30:33') as int2,
   SECOND(time2) as int3,
   SECOND(timestamp1) as int4,
   SECOND(CAST(time1 AS TIME)) as int5,
   SECOND(CAST(datetime1 AS TIMESTAMP)) as int6
  FROM T1;   

• 测试结果

  int1(BIGINT)	int2(BIGINT)	int3(BIGINT)	int4(BIGINT)	int5(BIGINT)	int6(BIGINT)
  33	33	24	13	24	13