SQL时间差计算查询异常求助：无法生成ride_length列

问题描述 #

尝试为clean_ttd表添加名为ride_length的计算列，用于计算started_at与ended_at的时间差，但执行SQL后未得到预期结果。

使用的SQL语句 #

ALTER TABLE clean_ttd
ADD COLUMN ride_length AS
  (TIME(strftime('%s',ended_at) - strftime('%s',started_at), 'unixepoch'))

数据表示例 #

问题原因 #

1. 年份超出Unix时间戳支持范围：数据中年份为0023（公元23年），而strftime('%s', datetime)仅能处理1970年1月1日之后的日期，更早的日期会返回NULL，导致减法运算结果为NULL，最终TIME函数无法生成有效结果。
2. TIME函数使用逻辑错误：即使时间戳有效，TIME(秒数, 'unixepoch')的作用是将秒数转换为1970-01-01 00:00:00之后的具体时间点，而非两个时间的差值。例如300秒会被转换为1970-01-01 00:05:00，而非预期的00:05:00时间差。

解决方案 #

针对SQLite（从语法判断使用的是SQLite），可以用julianday函数计算时间差，再格式化为所需的时间格式：

方案1：仅显示HH:MM:SS格式（适用于时间差小于24小时） #

ALTER TABLE clean_ttd
ADD COLUMN ride_length AS
  (strftime('%H:%M:%S', (julianday(ended_at) - julianday(started_at)) * 86400, 'unixepoch'));

方案2：显示天数+HH:MM:SS格式（支持超过24小时的时间差） #

ALTER TABLE clean_ttd
ADD COLUMN ride_length AS
  (
    CASE 
      WHEN julianday(ended_at) >= julianday(started_at) THEN
        printf('%d days %02d:%02d:%02d',
               CAST((julianday(ended_at) - julianday(started_at)) AS INTEGER),
               CAST((julianday(ended_at) - julianday(started_at)) * 24 AS INTEGER) % 24,
               CAST((julianday(ended_at) - julianday(started_at)) * 1440 AS INTEGER) % 60,
               CAST((julianday(ended_at) - julianday(started_at)) * 86400 AS INTEGER) % 60
             )
      ELSE 'Negative duration'
    END
  );

验证结果 #

以数据示例第一行为例，时间差为3分钟，方案1会返回00:03:00，方案2会返回0 days 00:03:00，符合预期。

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