MINUTE - 云原生大数据计算服务 MaxCompute

返回日期分钟部分的值。此函数为MaxCompute 2.0扩展函数。

命令格式

int minute(datetime|timestamp|string <date>)

参数说明

date：必填。DATETIME、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。

返回值说明

返回INT类型。返回规则如下：

date非DATETIME、TIMESTAMP或STRING类型，或格式不符合要求时，返回NULL。
date值为NULL时，返回NULL。

示例数据

为便于理解各函数的使用方法，本文为您提供源数据，基于源数据提供函数相关示例。创建表mf_date_fun_t，并添加数据，命令示例如下。

create table if not exists mf_date_fun_t(
    id      int,
    date1   date,
    datetime1   datetime,
    timestamp1 timestamp,
    date2   date,
    datetime2   datetime,
    timestamp2 timestamp,
    date3 string,
    date4 bigint);
insert into mf_date_fun_t values
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);

查询表mf_date_fun_t中的数据，命令示例如下：

select * from mf_date_fun_t;
--返回结果。
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| id         | date1      | datetime1           | timestamp1              | date2      | datetime2           | timestamp2              | date3      | date4      |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 1          | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780  |
| 2          | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781  |
| 3          | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782  |
| 4          | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783  |
| 5          | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784  |
| 6          | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785  |
| 7          | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786  |
| 8          | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787  |
| 9          | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788  |
| 10         | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-29 | 123456789  |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+

使用示例：静态数据示例

--返回30。
select minute('2014-09-01 12:30:00'); 
--返回30。
select minute('12:30:00');
--返回NULL。
select minute('20140901120000');
--返回NULL。
select minute(null);

使用示例：表数据示例

基于示例数据，返回datetime1和timestamp1分钟部分的值，命令示例如下。

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select datetime1, minute(datetime1) as datetime1_minute, timestamp1, minute(timestamp1) as timestamp1_minute from mf_date_fun_t;

返回结果如下。

+---------------------+------------------+-------------------------------+-------------------+
| datetime1           | datetime1_minute | timestamp1                    | timestamp1_minute |
+---------------------+------------------+-------------------------------+-------------------+
| 2021-11-29 00:01:00 | 1                | 2021-01-11 00:00:00.123456789 | 0                 |
| 2021-11-28 00:02:00 | 2                | 2021-02-11 00:00:00.123456789 | 0                 |
| 2021-11-27 00:03:00 | 3                | 2021-03-11 00:00:00.123456789 | 0                 |
| 2021-11-26 00:04:00 | 4                | 2021-04-11 00:00:00.123456789 | 0                 |
| 2021-11-25 00:05:00 | 5                | 2021-05-11 00:00:00.123456789 | 0                 |
| 2021-11-24 00:06:00 | 6                | 2021-06-11 00:00:00.123456789 | 0                 |
| 2021-11-23 00:07:00 | 7                | 2021-07-11 00:00:00.123456789 | 0                 |
| 2021-11-22 00:08:00 | 8                | 2021-08-11 00:00:00.123456789 | 0                 |
| 2021-11-21 00:09:00 | 9                | 2021-09-11 00:00:00.123456789 | 0                 |
| 2021-11-20 00:10:00 | 10               | 2021-10-11 00:00:00.123456789 | 0                 |
+---------------------+------------------+-------------------------------+-------------------+

云原生大数据计算服务 MaxCompute：MINUTE