如何在SQL/R中将字符串转为日期时间格式？解决仅获年份问题

你当前的SQL只提取了日期部分且未保留时间，同时存在月份拼接丢失前导零的风险，导致无法完整转换为可排序的日期时间格式。以下是修正后的方案：

完整转换并排序的SQL #

dbGetQuery(db2, "SELECT text,
  CAST(
      SUBSTR(created_at, -4) || '-' ||
      CASE SUBSTR(created_at, 5, 3)
         WHEN 'Jan' THEN '01'
         WHEN 'Feb' THEN '02'
         WHEN 'Mar' THEN '03'
         WHEN 'Apr' THEN '04'
         WHEN 'May' THEN '05'
         WHEN 'Jun' THEN '06'
         WHEN 'Jul' THEN '07'
         WHEN 'Aug' THEN '08'
         WHEN 'Sep' THEN '09'
         WHEN 'Oct' THEN '10'
         WHEN 'Nov' THEN '11'
         WHEN 'Dec' THEN '12'
      END || '-' ||
      SUBSTR(created_at, 9, 2) || ' ' ||
      SUBSTR(created_at, 12, 8)
   AS TIMESTAMP) AS created_at_ts
FROM tweets
ORDER BY created_at_ts")

修改说明 #

• 将CASE语句的返回值改为字符串形式（如'01'而非01），避免数字拼接时丢失前导零，确保月份始终为两位格式
• 添加时间部分提取：SUBSTR(created_at, 12, 8)，通过空格与日期部分拼接，保留完整的YYYY-MM-DD HH:MM:SS格式
• 转换目标类型改为TIMESTAMP（适配多数数据库的日期时间类型），确保字段可用于排序、比较操作
• 为转换后的字段命名created_at_ts，方便后续引用

查询最早/最晚时间戳 #

如果直接需要获取短信的最早和最晚时间，可使用聚合函数：

dbGetQuery(db2, "SELECT
  MIN(CAST(
      SUBSTR(created_at, -4) || '-' ||
      CASE SUBSTR(created_at, 5, 3)
         WHEN 'Jan' THEN '01'
         WHEN 'Feb' THEN '02'
         WHEN 'Mar' THEN '03'
         WHEN 'Apr' THEN '04'
         WHEN 'May' THEN '05'
         WHEN 'Jun' THEN '06'
         WHEN 'Jul' THEN '07'
         WHEN 'Aug' THEN '08'
         WHEN 'Sep' THEN '09'
         WHEN 'Oct' THEN '10'
         WHEN 'Nov' THEN '11'
         WHEN 'Dec' THEN '12'
      END || '-' ||
      SUBSTR(created_at, 9, 2) || ' ' ||
      SUBSTR(created_at, 12, 8)
   AS TIMESTAMP)) AS earliest_time,
  MAX(CAST(
      SUBSTR(created_at, -4) || '-' ||
      CASE SUBSTR(created_at, 5, 3)
         WHEN 'Jan' THEN '01'
         WHEN 'Feb' THEN '02'
         WHEN 'Mar' THEN '03'
         WHEN 'Apr' THEN '04'
         WHEN 'May' THEN '05'
         WHEN 'Jun' THEN '06'
         WHEN 'Jul' THEN '07'
         WHEN 'Aug' THEN '08'
         WHEN 'Sep' THEN '09'
         WHEN 'Oct' THEN '10'
         WHEN 'Nov' THEN '11'
         WHEN 'Dec' THEN '12'
      END || '-' ||
      SUBSTR(created_at, 9, 2) || ' ' ||
      SUBSTR(created_at, 12, 8)
   AS TIMESTAMP)) AS latest_time
FROM tweets")

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