UNIX

2023-08-19 06:22| 来源: 网络整理| 查看: 265

命令格式bigint unix_timestamp(datetime|date|timestamp|string )参数说明

date：必填。DATETIME、DATE、TIMESTAMP或STRING类型日期值，格式为yyyy-mm-dd、yyyy-mm-dd hh:mi:ss或yyyy-mm-dd hh:mi:ss.ff3。如果输入为STRING类型，且MaxCompute项目的数据类型版本是1.0，则会隐式转换为DATETIME类型后参与运算。当打开新数据类型属性时，隐式转换会失败，此时需要通过cast函数转换或关闭新数据类型，例如unix_timestamp(cast(... as datetime))。

返回值说明

返回BIGINT类型，表示UNIX格式日期值。返回规则如下：

date非DATETIME、DATE、TIMESTAMP或STRING类型，或格式不符合要求时，返回报错。

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 | +------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+使用示例：静态数据示例--返回1237518660。 select unix_timestamp(datetime'2009-03-20 11:11:00'); --返回1237518660。 set odps.sql.type.system.odps2=false; select unix_timestamp('2009-03-20 11:11:00'); --返回NULL。 select unix_timestamp(null);使用示例：表数据示例

基于示例数据，将date1、datetime1和timestamp1列日期转换为整型的UNIX格式的日期值，命令示例如下。

--开启2.0新类型。此命令需要与SQL语句一起提交。 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;

返回结果如下。

+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+ | 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 | +------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+相关函数

UNIX_TIMESTAMP函数属于日期函数，更多日期计算、日期转换的相关函数请参见日期函数。

【本文地址】

UNIX

UNIX

今日新闻

推荐新闻