PostgreSQL筛选每月10日照片并截取描述的技术问询

我有一张名为photos的表，包含description（描述）和capture_date（拍摄日期）字段，需要完成以下操作：

• 提取任意月份10日拍摄的照片
• 将描述内容截取至最多10字符，原内容超过10字符时添加省略号
• 将capture_date格式化为DD.MM HH24:MI格式

以下是我尝试的查询语句：

SELECT
  CASE
    WHEN LENGTH(description) > 10 THEN SUBSTRING(description, 1, 7) || '...'
    ELSE description
  END AS summary,
  TO_CHAR(capture_date, 'DD.MM HH24:MI') AS date
FROM photos
WHERE EXTRACT(DAY FROM capture_date) = 10
ORDER BY capture_date DESC;

问题修正与优化 #

你的核心思路没问题，但有几个细节可以调整：

1. 字符长度统计：LENGTH函数在部分数据库（如PostgreSQL）中按字节统计，遇到中文这类多字节字符时会判断错误，建议改用CHAR_LENGTH按字符数统计。
2. 关键字别名：date是部分数据库的关键字，作为字段别名可能引发语法问题，建议改为formatted_date这类更清晰的名称。
3. 跨数据库兼容：如果使用MySQL，TO_CHAR需要替换为DATE_FORMAT，EXTRACT可以简化为DAY函数。

适配PostgreSQL的修正版SQL

SELECT
  CASE
    WHEN CHAR_LENGTH(description) > 10 THEN SUBSTRING(description, 1, 7) || '...'
    ELSE description
  END AS summary,
  TO_CHAR(capture_date, 'DD.MM HH24:MI') AS formatted_date
FROM photos
WHERE EXTRACT(DAY FROM capture_date) = 10
ORDER BY capture_date DESC;

适配MySQL的修正版SQL

SELECT
  CASE
    WHEN CHAR_LENGTH(description) > 10 THEN CONCAT(SUBSTRING(description, 1, 7), '...')
    ELSE description
  END AS summary,
  DATE_FORMAT(capture_date, '%d.%m %H:%i') AS formatted_date
FROM photos
WHERE DAY(capture_date) = 10
ORDER BY capture_date DESC;

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