DATE_FORMAT函數介紹 - MaxCompute

DATE_FORMAT支援將DATE、TIMESTAMP和STRING類型的資料，轉換為指定格式的字串。本文介紹DATE_FORMAT函數的命令格式和使用樣本。

命令格式

string date_format(date|timestamp|string <date>, string <format>)

參數說明

date：必填。待轉換的日期值。支援DATE、TIMESTAMP或STRING類型。
- DATE和STRING類型只能在Hive相容模式下使用，您可以執行set odps.sql.hive.compatible=true;命令開啟Hive模式。
- 如果參數為STRING類型，則格式只支援以下三種日期欄位：
  - 'yyyy-MM-dd'，例如'2019-12-27'。
  - 'yyyy-MM-dd hh:mm:ss'，例如'2019-12-27 12:23:10'。
  - 'yyyy-MM-dd hh:mm:ss.SSS'，例如'2019-12-27 12:23:10.123'。
format：必填。STRING類型常量。format可由如下日期欄位組成，例如yyyy-MM-dd hh:mm:ss.SSS或yyyy-MM-dd hh:mi:ss.SSS：
- YYYY或yyyy代表年份，其中yyyy代表日曆年度，YYYY代表周年。
  說明
  周年格式可能會與實際年份不一致，請謹慎使用。
- MM代表月份。
- mm代表分鐘。
- dd代表天。
- HH代表24小時制時。
- hh代表12小時制時。
- mi代表分鐘。
- ss代表秒。
- SSS代表毫秒。
重要
- 在非Hive相容模式下，HH和hh都代表24小時制時。並且只能使用yyyy-MM-dd hh:mi:ss，如果使用yyyy-MM-dd hh:mm:ss，mm部分會取月份值。
- 在Hive相容模式下，HH代表24小時制時，hh代表12小時制時。並且只能使用yyyy-MM-dd hh:mm:ss，如果使用yyyy-MM-dd hh:mi:ss會返回NULL。

傳回值說明

返回STRING類型。返回規則如下：

date非DATE或TIMESTAMP類型時，返回NULL。
date值為NULL時，返回報錯。
format值為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  |
+------+-------+------------+------------+-------+------------+------------+-------+------------+

使用樣本：待用資料樣本

--開啟Hive模式。與SQL語句一起提交。
set odps.sql.hive.compatible=true;
--假設目前時間為2023-11-20 17:52。返回2023-11-20 17:52:41.544。
select date_format(from_utc_timestamp(current_timestamp(), 'UTC'),'yyyy-MM-dd HH:mm:ss.SSS');
--返回2022-04-24。
select date_format('2022-04-24','yyyy-MM-dd');

使用樣本：表資料樣本

基於樣本資料，對datetime1和timestamp1轉換為指定格式的字串，命令樣本如下。

--開啟Hive模式並開啟2.0新類型。與SQL語句一起提交。
set odps.sql.hive.compatible=true;
set odps.sql.type.system.odps2=true;
select datetime1, timestamp1, date_format(datetime1,'yyyy/mm/dd'),
       date_format(datetime1,'yyyy/mm/dd HH:mm:ss'),
       date_format(timestamp1,'yyyy/mm/dd HH:mm:ss')
from mf_date_fun_t;

返回結果如下。

+------------+------------+-----+-----+-----+
| datetime1  | timestamp1 | _c2 | _c3 | _c4 |
+------------+------------+-----+-----+-----+
| 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021/01/29 | 2021/01/29 00:01:00 | 2021/00/11 00:00:00 |
| 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021/02/28 | 2021/02/28 00:02:00 | 2021/00/11 00:00:00 |
| 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021/03/27 | 2021/03/27 00:03:00 | 2021/00/11 00:00:00 |
| 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021/04/26 | 2021/04/26 00:04:00 | 2021/00/11 00:00:00 |
| 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021/05/25 | 2021/05/25 00:05:00 | 2021/00/11 00:00:00 |
| 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021/06/24 | 2021/06/24 00:06:00 | 2021/00/11 00:00:00 |
| 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021/07/23 | 2021/07/23 00:07:00 | 2021/00/11 00:00:00 |
| 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021/08/22 | 2021/08/22 00:08:00 | 2021/00/11 00:00:00 |
| 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021/09/21 | 2021/09/21 00:09:00 | 2021/00/11 00:00:00 |
| 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021/10/20 | 2021/10/20 00:10:00 | 2021/00/11 00:00:00 |
+------------+------------+-----+-----+-----+

MaxCompute：DATE_FORMAT