Converts a date value into a UNIX timestamp that is an integer.
Syntax
bigint unix_timestamp(datetime|date|timestamp|string <date>)Parameters
date: required. A date value of the DATETIME, DATE, TIMESTAMP, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss.ff3 format. If the input value is of the STRING type and the MaxCompute V1.0 data type edition is used in your project, the input value is implicitly converted into the DATETIME type before calculation. If you enable the MaxCompute V2.0 data type edition, the implicit conversion fails. In this case, you must use the CAST function, such as unix_timestamp(cast(... as datetime)), to convert data types. You can also disable the MaxCompute V2.0 data type edition.
Return value
A UNIX timestamp of the BIGINT type is returned. The return value varies based on the following rules:
If the value of date is not of the DATETIME, DATE, TIMESTAMP, or STRING type or the format does not meet the requirements, an error is returned.
If the value of date is null, null is returned.
Sample data
This section provides sample source data for you to understand how to use date functions. In this topic, a table named mf_date_fun_t is created and data is inserted into the table. Sample statements:
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);Query data from the mf_date_fun_t table. Sample statement:
select * from mf_date_fun_t;
-- The following result is returned:
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 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 |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+Example: static data
-- The return value is 1237518660.
select unix_timestamp(datetime'2009-03-20 11:11:00');
-- The return value is 1237518660.
set odps.sql.type.system.odps2=false;
select unix_timestamp('2009-03-20 11:11:00');
-- The return value is null.
select unix_timestamp(null);Example: table data
Convert date values in the date1, datetime1, and timestamp1 columns into UNIX timestamps that are integers. Data in Sample data is used in this example. Sample statement:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with the SQL statement.
set odps.sql.type.system.odps2=true;
select date1, unix_timestamp(date1) as date1_unix_timestamp, datetime1, unix_timestamp(datetime1) as datetime1_unix_timestamp, timestamp1, unix_timestamp(timestamp1) as timestamp1_unix_timestamp from mf_date_fun_t;The following result is returned:
+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+
| date1 | date1_unix_timestamp | datetime1 | datetime1_unix_timestamp | timestamp1 | timestamp1_unix_timestamp |
+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+
| 2021-11-29 | 1638115200 | 2021-11-29 00:01:00 | 1638115260 | 2021-01-11 00:00:00.123456789 | 1610294400 |
| 2021-11-28 | 1638028800 | 2021-11-28 00:02:00 | 1638028920 | 2021-02-11 00:00:00.123456789 | 1612972800 |
| 2021-11-27 | 1637942400 | 2021-11-27 00:03:00 | 1637942580 | 2021-03-11 00:00:00.123456789 | 1615392000 |
| 2021-11-26 | 1637856000 | 2021-11-26 00:04:00 | 1637856240 | 2021-04-11 00:00:00.123456789 | 1618070400 |
| 2021-11-25 | 1637769600 | 2021-11-25 00:05:00 | 1637769900 | 2021-05-11 00:00:00.123456789 | 1620662400 |
| 2021-11-24 | 1637683200 | 2021-11-24 00:06:00 | 1637683560 | 2021-06-11 00:00:00.123456789 | 1623340800 |
| 2021-11-23 | 1637596800 | 2021-11-23 00:07:00 | 1637597220 | 2021-07-11 00:00:00.123456789 | 1625932800 |
| 2021-11-22 | 1637510400 | 2021-11-22 00:08:00 | 1637510880 | 2021-08-11 00:00:00.123456789 | 1628611200 |
| 2021-11-21 | 1637424000 | 2021-11-21 00:09:00 | 1637424540 | 2021-09-11 00:00:00.123456789 | 1631289600 |
| 2021-11-20 | 1637337600 | 2021-11-20 00:10:00 | 1637338200 | 2021-10-11 00:00:00.123456789 | 1633881600 |
+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+Related functions
UNIX_TIMESTAMP is a date function. For more information about functions related to date computing and conversion, see Date functions.