语法
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
语法
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
语法
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
语法
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
语法
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
语法
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
语法
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