DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference Development reference: SQL Function reference Built-in functions Date and Time Functions MINUTE

MINUTE

Updated at:
Copy as MD
Product
My Favorites

Returns the minute component of a date value as an integer. MINUTE is an additional function of MaxCompute V2.0.

Syntax

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

Parameters

date: Required. A DATETIME, TIMESTAMP, or STRING value. STRING values must match one of these formats:

  • yyyy-mm-dd hh:mi:ss

  • yyyy-mm-dd hh:mi:ss.ff3

Return value

Returns an INT value.

Returns NULL if:

  • date is NULL

  • date is not a DATETIME, TIMESTAMP, or STRING value

  • A STRING value does not match the required format

Usage notes

To use MINUTE with DATETIME or TIMESTAMP columns, enable the MaxCompute V2.0 data type edition by running the following command before your SQL statement:

set odps.sql.type.system.odps2=true;

Examples

Static values

-- Returns 30
SELECT minute('2014-09-01 12:30:00');

-- Returns 30 (time-only string)
SELECT minute('12:30:00');

-- Returns NULL (format does not match)
SELECT minute('20140901120000');

-- Returns NULL
SELECT minute(null);

Table data

The following examples use the mf_date_fun_t sample table. To create and populate the table, run:

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);

Extract the minute component from the datetime1 and timestamp1 columns:

-- Enable the MaxCompute V2.0 data type edition. Run this with the SQL statement.
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;

Result:

+---------------------+------------------+-------------------------------+-------------------+
| 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                 |
+---------------------+------------------+-------------------------------+-------------------+

Related functions

MINUTE is a date function. For other date and time functions, see Date functions.

Previous：LASTDAYNext：MONTH