DuckDB : 日期函数及常用日期转换操作

本文链接：https://blog.csdn.net/neweastsun/article/details/144971665

数据工程师最常见操作是针对日期类型的，可能是将STRING日期转换为实际日期，或抽取日期单元以及日期运算等。幸运的是，DuckDB提供完整的解决方案，本文介绍最常用日期操作相关的函数，并给出示例。

日期转换

strptime

STRPTIME函数用于将一个日期 / 时间字符串按照指定的格式解析为日期 / 时间类型。它能够把各种格式的日期和时间字符串转换为 DuckDB 内部可以处理的日期或时间格式。

它接受两个参数。第一个参数是要转换的日期 / 时间字符串，第二个参数是一个格式化字符串，用于指定第一个参数中日期 / 时间部分的格式。

SELECT STRPTIME('05/08/2022', '%m/%d/%Y');

在这个例子中，%m表示月份（01 - 12），%d表示日期（01 - 31），%Y表示 4 位年份（如 2022）。STRPTIME函数会按照这个格式来解析输入的字符串，将其转换为 DuckDB 中的日期格式。

如果日期字符串是'08-05-2022'（格式为日 - 月 - 年），并且你想将其转换为日期类型，代码如下：

SELECT STRPTIME('08 - 05 - 2022', '%d - %m - %Y');

完整日期类型，只需要指定好格式：

D select strptime('2022-01-13 11:59:47', '%Y-%m-%d %H:%M:%S') ;
┌──────────────────────────────────────────────────────┐
│ strptime('2022-01-13 11:59:47', '%Y-%m-%d %H:%M:%S') │
│                      timestamp                       │
├──────────────────────────────────────────────────────┤
│ 2022-01-13 11:59:47                                  │
└──────────────────────────────────────────────────────┘

还需要注意的是，DuckDB有一个CSV自动加载器，它可以很好地感知格式，包括复杂的日期时间格式。下面是cast函数直接进行转换：

D select cast('2022-01-13 11:59:47' as timestamp) ;
┌──────────────────────────────────────────┐
│ CAST('2022-01-13 11:59:47' AS TIMESTAMP) │
│                timestamp                 │
├──────────────────────────────────────────┤
│ 2022-01-13 11:59:47                      │
└──────────────────────────────────────────┘

strftime

STRFTIME函数的作用与STRPTIME相反。它用于将日期 / 时间类型的数据按照指定的格式转换为字符串。这在需要将日期 / 时间以特定格式展示给用户或者用于外部输出等场景非常有用。

它也接受两个参数。第一个参数是日期 / 时间类型的数据（如一个日期列或者一个日期变量），第二个参数是一个格式化字符串，用于指定输出日期 / 时间字符串的格式。

假设你有一个日期列date_column，其中存储了日期数据，你想将其格式化为'YYYY - MM - DD'的字符串格式。可以使用以下代码：

SELECT STRFTIME(date_column, '%Y-%m-%d') FROM your_table;

这里%Y表示 4 位年份，%m表示月份（01 - 12），%d表示日期（01 - 31）。通过STRFTIME函数，日期数据会按照指定的格式转换为字符串输出。

例如，如果你有一个日期值'2022-05-08'，并且你想将其转换为'May 8th, 2022'的格式，可以使用代码：

SELECT STRFTIME('2022-05-08', '%B %dth, %Y');

其中%B表示月份的全称（如 January、February 等），%dth用于表示日期并带上th（如 1st、2nd、3rd、4th 等），%Y表示 4 位年份。

日期运算

我们经常遇到的是简单的日的加减法。很多时候发现自己有计划，我们想回到30天前，或者提前30天。情况下面示例：

D select cast('2022-1-3' as date) + 5 ;
┌────────────────────────────────┐
│ (CAST('2022-1-3' AS DATE) + 5) │
│              date              │
├────────────────────────────────┤
│ 2022-01-08                     │
└────────────────────────────────┘

这还真有趣，让人耳目一新。简单地使用加法+操作符来添加一些天数，这一点很简单。DuckDB也有一个普通的方法，如果你想要显式地添加日期、月份或其他……使用date_add()方法。

D select date_add(cast('2022-1-3' as date), interval 30 days) as datey ;
┌─────────────────────┐
│        datey        │
│      timestamp      │
├─────────────────────┤
│ 2022-02-02 00:00:00 │
└─────────────────────┘

D select date_add(cast('2022-1-3' as date), interval 2 weeks) as datey ;
┌─────────────────────┐
│        datey        │
│      timestamp      │
├─────────────────────┤
│ 2022-01-17 00:00:00 │
└─────────────────────┘

D select date_add(cast('2022-1-3' as date), interval 1 months) as datey ;
┌─────────────────────┐
│        datey        │
│      timestamp      │
├─────────────────────┤
│ 2022-02-03 00:00:00 │
└─────────────────────┘

datediff

计算两个日期之差，并按照指定单元返回，入天、月等；

D select date_diff('day',cast('2022-1-3' as date), cast('2023-1-4' as date)) as datey ;
┌───────┐
│ datey │
│ int64 │
├───────┤
│   366 │
└───────┘

D select date_diff('month',cast('2022-1-3' as date), cast('2023-1-4' as date)) as datey ;
┌───────┐
│ datey │
│ int64 │
├───────┤
│    12 │
└───────┘

抽取 Date 单元

我们经常做的另一个日期魔术就是把日期每个单元抽出来，比如一年、一个月、一个月的哪一天等等。

D select date_part('year',cast('2022-1-3' as date)) as datey ;
┌───────┐
│ datey │
│ int64 │
├───────┤
│  2022 │
└───────┘

D select date_part('month',cast('2022-1-3' as date)) as datey ;
┌───────┐
│ datey │
│ int64 │
├───────┤
│     1 │
└───────┘

D select date_part('day',cast('2022-1-3' as date)) as datey ;
┌───────┐
│ datey │
│ int64 │
├───────┤
│     3 │
└───────┘

奇妙的是，DuckDB中有更多的日期和日期时间的东西，几乎太多了，这里列举一些可能会在工作中派上用场的几个函数。

dayname

D select dayname(cast('2022-1-3' as date)) as datey ;
┌─────────┐
│  datey  │
│ varchar │
├─────────┤
│ Monday  │
└─────────┘

last_day

D select last_day(cast('2022-1-3' as date)) as datey ;
┌────────────┐
│   datey    │
│    date    │
├────────────┤
│ 2022-01-31 │
└────────────┘

总结

本文介绍DuckDB常用日期函数，包括日期转换、日期运算、日期单元抽取等。更多日期函数介绍，请参考官方文档。