如何在DuckDB中基于起止日期列生成10分钟间隔的时间戳序列？

原始数据 #

目标效果 #

生成原始起止时间之间间隔10分钟的时间戳序列，并关联原始起止时间列，结果示例如下：

PostgreSQL实现代码 #

with date_range as (
  select start_timestamp, date('2020-01-01') as stop_timestamp
  from
    pg_catalog.generate_series('2012-01-01', '2020-01-01', interval '3 years') as start_timestamp
)
select
  timestamp,
  start_timestamp,
  stop_timestamp
from
  date_range,
  pg_catalog.generate_series(start_timestamp, stop_timestamp, interval '10 minutes') as timestamp 

问题：DuckDB模仿写法失败 #

尝试的DuckDB代码：

WITH date_range AS (
    SELECT
      generate_series as start_timestamp,
      CAST('2020-01-01' AS DATE) as stop_timestamp
    FROM
      generate_series(TIMESTAMP '2012-01-01', TIMESTAMP '2020-01-01', INTERVAL '3 years')
)
SELECT
  start_timestamp,
  stop_timestamp,
  timestamp
FROM
  date_range,
  generate_series(TIMESTAMP start_timestamp, TIMESTAMP stop_timestamp, INTERVAL '10 minute')

DuckDB解决方案 #

DuckDB中generate_series默认生成数组类型，需通过unnest展开或横向连接实现行级序列生成，以下是两种可行写法：

写法1：使用UNNEST展开序列 #

WITH date_range AS (
    SELECT
        generate_series AS start_timestamp,
        CAST('2020-01-01' AS TIMESTAMP) AS stop_timestamp
    FROM
        generate_series(TIMESTAMP '2012-01-01', TIMESTAMP '2020-01-01', INTERVAL '3 years')
)
SELECT
    unnest(generate_series(start_timestamp, stop_timestamp, INTERVAL '10 minutes')) AS timestamp,
    start_timestamp,
    stop_timestamp
FROM date_range;

写法2：使用LATERAL JOIN #

WITH date_range AS (
    SELECT
        generate_series AS start_timestamp,
        CAST('2020-01-01' AS TIMESTAMP) AS stop_timestamp
    FROM
        generate_series(TIMESTAMP '2012-01-01', TIMESTAMP '2020-01-01', INTERVAL '3 years')
)
SELECT
    ts.timestamp,
    dr.start_timestamp,
    dr.stop_timestamp
FROM date_range dr
CROSS JOIN LATERAL (
    SELECT generate_series(dr.start_timestamp, dr.stop_timestamp, INTERVAL '10 minutes') AS timestamp
) ts;

关键说明 #

1. 确保start_timestamp和stop_timestamp统一为TIMESTAMP类型，避免类型不匹配报错；
2. DuckDB中需显式拆分generate_series生成的数组，unnest或LATERAL JOIN是两种标准方式。

内容的提问来源于stack exchange，提问作者rdmolony