如何将INTERVAL类型转换为DATETIME/TIMESTAMP？解决日期提取问题

问题描述 #

我尝试计算两个TIMESTAMP类型列（started_at和ended_at）的差值，结果返回INTERVAL类型。我希望将该结果转换为TIMESTAMP或TIME等类型，同时当前因trip_duration为INTERVAL类型，无法从中提取DAYOFWEEK，请问该如何解决？当前使用的SQL代码如下：

SELECT 
  member_casual,
 AVG(trip_duration) AS avg_trip_duration,
 EXTRACT(DAYOFWEEK FROM trip_duration) AS dayofweek

FROM(
  SELECT
  member_casual,
  ended_at - started_at AS trip_duration
  FROM `helical-fin-439209-b4.cyclistic.202201_tripdata`)

GROUP BY member_casual,trip_duration
ORDER BY avg_trip_duration DESC;

解决方案 #

1. 转换INTERVAL为TIME或数值类型 #

INTERVAL代表的是时间间隔，不是具体的时间点，转成TIMESTAMP没有实际业务意义（TIMESTAMP指向某一个具体的日期时刻）。如果需要可视化或计算时长，推荐两种转换方式：

• 转TIME类型：适用于时长不超过24小时的场景，直接用TIME()函数转换：

  SELECT
    member_casual,
    TIME(ended_at - started_at) AS trip_duration_time
  FROM `helical-fin-439209-b4.cyclistic.202201_tripdata`
  

• 转总秒数/分钟数：支持超过24小时的时长，计算总时长的数值形式：

  SELECT
    member_casual,
    -- 计算总秒数
    EXTRACT(DAY FROM trip_duration)*86400 + 
    EXTRACT(HOUR FROM trip_duration)*3600 + 
    EXTRACT(MINUTE FROM trip_duration)*60 + 
    EXTRACT(SECOND FROM trip_duration) AS trip_duration_seconds
  FROM(
    SELECT
      member_casual,
      ended_at - started_at AS trip_duration
    FROM `helical-fin-439209-b4.cyclistic.202201_tripdata`
  )
  

2. 正确提取DAYOFWEEK #

你无法从INTERVAL中提取DAYOFWEEK，因为这个函数是针对具体日期的属性，而非时间间隔。正确的做法是从行程的开始或结束时间（started_at/ended_at）提取星期几，再按用户类型和星期几分组计算平均时长，修正后的SQL如下：

SELECT 
  member_casual,
  EXTRACT(DAYOFWEEK FROM started_at) AS dayofweek,
  AVG(ended_at - started_at) AS avg_trip_duration
FROM `helical-fin-439209-b4.cyclistic.202201_tripdata`
GROUP BY member_casual, dayofweek
ORDER BY avg_trip_duration DESC;

原代码中按trip_duration分组是错误的逻辑——这会把每个不同时长的行程单独分组，无法得到有意义的平均时长统计。

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