DATEADD

更新时间: 2023-06-15 10:57:10

按照指定的单位datepart和幅度delta修改date(日期)的值。

注意事项

如果您需要获取在当前时间基础上指定变动幅度的日期,请结合GETDATE函数使用。

命令格式

date|datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>)

参数说明

  • date:必填。日期值,DATE、DATETIME或TIMESTAMP类型。

    如果参数为STRING类型,格式符合DATETIME类型的格式,即yyyy-mm-dd hh:mi:ss,例如2021-08-28 00:00:00,且MaxCompute项目的数据类型版本是1.0,则会隐式转换为DATETIME类型后参与运算。

  • delta:必填。修改幅度,BIGINT类型。如果delta大于0,则增,否则减。

    如果参数为STRING或DOUBLE类型,则会隐式转换为BIGINT类型后参与运算。

    说明
    • 按照指定的单位增减delta时,会导致更高单位的进位或退位。年、月、时、分秒分别按照10进制、12进制、24进制、60进制进行计算。

    • delta的单位是月时,如果delta的月部分在增加delta值之后不造成Day溢出,则保持Day值不变,否则将Day值设置为结果月份的最后1天。

  • datepart:必填。指定修改的单位,STRING类型常量。非常量、不支持的格式或其他类型会返回报错。

    此字段的取值遵循STRING与DATETIME类型转换的约定,即yyyy表示年,mm表示月,dd表示天。关于类型转换的规则,详情请参见数据类型转换。该字段也支持扩展的日期格式:年-year、月-month-mon、日-day和小时-hour

返回值说明

返回DATE或DATETIME类型,格式为yyyy-mm-ddyyyy-mm-dd hh:mi:ss。返回规则如下:

  • date非DATE、DATETIME或TIMESTAMP类型时,返回报错。

  • date值为NULL时,返回报错。

  • deltadatepart值为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  |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+

使用示例:静态数据示例

  • 示例1:常见用法。

    --返回2005-03-01 00:00:00。加1天,结果超出当年2月份的最后一天,实际值为下个月的第1天。
    select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd');
    --返回2005-02-27 00:00:00。减1天。
    select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
    --返回2006-10-28 00:00:00。加20个月,月份溢出,年份加1。
    select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
    --返回2005-03-28 00:00:00。
    select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
    --返回2005-02-28 00:00:00。2005年2月没有29日,日期截取至当月最后1天。
    select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
    --返回2005-02-28 00:00:00。
    select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
    --返回2005-03-18。
    select dateadd(date '2005-02-18', 1, 'mm');
    --开启2.0新类型。此命令需要与SQL语句一起提交。返回2005-03-18 00:00:00.0。
    set odps.sql.type.system.odps2=true;
    select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm');
    --假设当前时间为2020-11-17 16:31:44,返回2020-11-16 16:31:44。
    select dateadd(getdate(),-1,'dd');
    --返回NULL。
    select dateadd(date '2005-02-18', 1, null);
  • 示例2:用常量表示。

    在MaxCompute SQL中,DATETIME类型没有直接的常量表示方式,错误命令示例如下:

    select dateadd(2005-03-30 00:00:00, -1, 'mm');

    如果需要描述DATETIME类型的常量,正确命令示例如下:

    --将STRING类型常量显式转换为DATETIME类型,返回2005-02-28 00:00:00。
    select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');
  • 示例3:输入参数为STRING类型。

    --如下输入参数为STRING类型,但不符合DATETIME类型格式,会返回报错。
    select dateadd('2021-08-27',1,'dd');
    --如下输入参数为STRING类型,符合DATETIME类型格式,且MaxCompute项目的数据类型版本为1.0,返回2005-03-01 00:00:00。
    set odps.sql.type.system.odps2=false;
    select dateadd('2005-02-28 00:00:00', 1, 'dd');

使用示例:表数据示例

基于示例数据,对date1、datetime1和timestamp1列按照指定单位和幅度变化,命令示例如下。

--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, dateadd(date1,1,'dd') as date1_dateadd, datetime1, dateadd(datetime1,1,'mm') as datetime1_dateadd, timestamp1, dateadd(timestamp1,-1,'yyyy') as timestamp1_dateadd from mf_date_fun_t;

返回结果如下。

+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| date1      | date1_dateadd | datetime1           | datetime1_dateadd   | timestamp1                    | timestamp1_dateadd            |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| 2021-11-29 | 2021-11-30    | 2021-11-29 00:01:00 | 2021-12-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2020-01-11 00:00:00.123456789 |
| 2021-11-28 | 2021-11-29    | 2021-11-28 00:02:00 | 2021-12-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2020-02-11 00:00:00.123456789 |
| 2021-11-27 | 2021-11-28    | 2021-11-27 00:03:00 | 2021-12-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2020-03-11 00:00:00.123456789 |
| 2021-11-26 | 2021-11-27    | 2021-11-26 00:04:00 | 2021-12-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2020-04-11 00:00:00.123456789 |
| 2021-11-25 | 2021-11-26    | 2021-11-25 00:05:00 | 2021-12-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2020-05-11 00:00:00.123456789 |
| 2021-11-24 | 2021-11-25    | 2021-11-24 00:06:00 | 2021-12-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2020-06-11 00:00:00.123456789 |
| 2021-11-23 | 2021-11-24    | 2021-11-23 00:07:00 | 2021-12-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2020-07-11 00:00:00.123456789 |
| 2021-11-22 | 2021-11-23    | 2021-11-22 00:08:00 | 2021-12-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2020-08-11 00:00:00.123456789 |
| 2021-11-21 | 2021-11-22    | 2021-11-21 00:09:00 | 2021-12-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2020-09-11 00:00:00.123456789 |
| 2021-11-20 | 2021-11-21    | 2021-11-20 00:10:00 | 2021-12-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2020-10-11 00:00:00.123456789 |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+

相关函数

DATEADD函数属于日期函数,更多日期计算、日期转换的相关函数请参见日期函数

上一篇: DATE_SUB 下一篇: DATEDIFF
阿里云首页 云原生大数据计算服务 MaxCompute 相关技术圈