如何在DB Browser for SQLite中将特定TEXT格式转为datetime并计算时间差？

CAST函数无法完成转换，是因为数据库无法自动识别1 Jul 2023 10:22:48这种自定义文本日期格式，需要用对应数据库的日期解析函数指定格式规则，以下是主流数据库的实现方法：

1. MySQL/MariaDB #

使用STR_TO_DATE函数匹配格式：

SELECT
  STR_TO_DATE(tracks.name, '%e %b %Y %T') AS datetime
FROM tracks;

格式符说明：

• %e：不带前导零的日期（如1、12）
• %b：英文月份缩写（如Jul、Jan）
• %Y：四位年份
• %T：时分秒格式（HH:MM:SS）

2. PostgreSQL #

使用TO_TIMESTAMP函数解析：

SELECT
  TO_TIMESTAMP(tracks.name, 'DD Mon YYYY HH24:MI:SS') AS datetime
FROM tracks;

注意：需确保数据库区域设置支持英文月份缩写，默认英文环境可直接使用。

3. SQL Server #

使用TRY_PARSE函数自动识别英文日期格式：

SELECT
  TRY_PARSE(tracks.name AS DATETIME USING 'en-US') AS datetime
FROM tracks;

该方法对这类带英文月份的格式支持最简便，无需手动指定格式符。

4. Oracle #

使用TO_DATE函数指定格式：

SELECT
  TO_DATE(tracks.name, 'DD MON YYYY HH24:MI:SS') AS datetime
FROM tracks;

Oracle默认不区分月份缩写的大小写，Jul或JUL都能正常匹配。

计算时间差示例 #

转换完成后，可使用对应数据库的时间差函数计算条目间的差值：

• MySQL：用TIMESTAMPDIFF计算秒级差值

SELECT
  TIMESTAMPDIFF(SECOND,
                STR_TO_DATE(t1.name, '%e %b %Y %T'),
                STR_TO_DATE(t2.name, '%e %b %Y %T')) AS time_diff_seconds
FROM tracks t1
JOIN tracks t2 ON t1.id = t2.id - 1; -- 按id关联前后条目，需根据实际业务调整关联条件

• PostgreSQL：直接用减法获取时间间隔

SELECT
  TO_TIMESTAMP(t2.name, 'DD Mon YYYY HH24:MI:SS') - TO_TIMESTAMP(t1.name, 'DD Mon YYYY HH24:MI:SS') AS time_diff
FROM tracks t1
JOIN tracks t2 ON t1.id = t2.id - 1;

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