全部產品
Search

搜尋本產品

    MaxCompute：DATEADD

    文件中心

    MaxCompute：DATEADD

    更新時間：Jun 19, 2024

    按照指定的單位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-dd或yyyy-mm-dd hh:mi:ss。返回規則如下：

    • date非DATE、DATETIME或TIMESTAMP類型時，返回報錯。

    • date值為NULL時，返回報錯。

    • delta或datepart值為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.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-20 | 123456780  |
| 2    | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-21 | 123456781  |
| 3    | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-22 | 123456782  |
| 4    | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-23 | 123456783  |
| 5    | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-24 | 123456784  |
| 6    | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-25 | 123456785  |
| 7    | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-26 | 123456786  |
| 8    | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-27 | 123456787  |
| 9    | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-28 | 123456788  |
| 10   | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 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函數屬於日期函數，更多日期計算、日期轉換的相關函數請參見日期函數。