在 MariaDB 中返回带英文序数后缀的日期(如 1st、2nd、3rd)

介绍在 MariaDB 中使用 DATE_FORMAT() 函数配合 %D 说明符,将日期中的天数格式化为带有英文序数词后缀(如 1st、2nd、12th、23rd)的详细用法与完整对照表。

发布于

在英文排版或面向海外用户的报表输出中,日期中的“日”常常需要表示为带有序数词后缀的格式,例如 1st、2nd、3rd、4th、21st 等。

在 MariaDB 中,无需编写繁琐的 CASE WHEN 逻辑,直接使用内置的 DATE_FORMAT() 函数并配合 %D 格式说明符,即可直接获得带有正确英文序数后缀的日期。


基本用法与示例

以下示例演示如何将日期 '2022-12-12' 格式化为包含英文序数后缀的完整日期字符串:

SELECT DATE_FORMAT('2022-12-12', '%D %M %Y');

输出:

+---------------------------------------+
| DATE_FORMAT('2022-12-12', '%D %M %Y') |
+---------------------------------------+
| 12th December 2022                    |
+---------------------------------------+

上述格式字符串中各说明符的作用如下:

  • %D:返回带有英文序数词后缀的天(如 1st, 2nd, 12th)。
  • %M:返回月份的全名(如 December)。
  • %Y:返回 4 位数字的年份(如 2022)。

常见天数格式说明符对比

为了更好地满足不同的业务排版需求,MariaDB 提供了三种获取“天”的说明符:

说明符 含义与示例 适用场景
%D 带有英文序数后缀的日(1st, 2nd, …, 31st) 英文文案、通知信件、新闻发布日期展示
%d 两位数字的日,不足两位前导补零(01 至 31) 标准 ISO 日期格式、结构化导出文件
%e 一位或两位数字的日,不补零(1 至 31) 日常紧凑展示

以下 SQL 语句展示了这三者的输出差异:

SELECT
  DATE_FORMAT('2022-12-03', '%D') AS "with_suffix",
  DATE_FORMAT('2022-12-03', '%d') AS "padded_zero",
  DATE_FORMAT('2022-12-03', '%e') AS "no_padding";

输出:

+-------------+-------------+------------+
| with_suffix | padded_zero | no_padding |
+-------------+-------------+------------+
| 3rd         | 03          | 3          |
+-------------+-------------+------------+

1 至 31 日英文序数后缀完整对照

在英语语法中,除特殊规律的 11th、12th、13th 之外,末尾数字为 1 用 st,2 用 nd,3 用 rd,其余数字用 th。MariaDB 的 %D 说明符已经完整内置了这些规则:

SELECT
  DATE_FORMAT('2022-12-01', '%D') AS "01",
  DATE_FORMAT('2022-12-02', '%D') AS "02",
  DATE_FORMAT('2022-12-03', '%D') AS "03",
  DATE_FORMAT('2022-12-04', '%D') AS "04",
  DATE_FORMAT('2022-12-05', '%D') AS "05",
  DATE_FORMAT('2022-12-06', '%D') AS "06",
  DATE_FORMAT('2022-12-07', '%D') AS "07",
  DATE_FORMAT('2022-12-08', '%D') AS "08",
  DATE_FORMAT('2022-12-09', '%D') AS "09",
  DATE_FORMAT('2022-12-10', '%D') AS "10",
  DATE_FORMAT('2022-12-11', '%D') AS "11",
  DATE_FORMAT('2022-12-12', '%D') AS "12",
  DATE_FORMAT('2022-12-13', '%D') AS "13",
  DATE_FORMAT('2022-12-14', '%D') AS "14",
  DATE_FORMAT('2022-12-15', '%D') AS "15",
  DATE_FORMAT('2022-12-16', '%D') AS "16",
  DATE_FORMAT('2022-12-17', '%D') AS "17",
  DATE_FORMAT('2022-12-18', '%D') AS "18",
  DATE_FORMAT('2022-12-19', '%D') AS "19",
  DATE_FORMAT('2022-12-20', '%D') AS "20",
  DATE_FORMAT('2022-12-21', '%D') AS "21",
  DATE_FORMAT('2022-12-22', '%D') AS "22",
  DATE_FORMAT('2022-12-23', '%D') AS "23",
  DATE_FORMAT('2022-12-24', '%D') AS "24",
  DATE_FORMAT('2022-12-25', '%D') AS "25",
  DATE_FORMAT('2022-12-26', '%D') AS "26",
  DATE_FORMAT('2022-12-27', '%D') AS "27",
  DATE_FORMAT('2022-12-28', '%D') AS "28",
  DATE_FORMAT('2022-12-29', '%D') AS "29",
  DATE_FORMAT('2022-12-30', '%D') AS "30",
  DATE_FORMAT('2022-12-31', '%D') AS "31"\G

输出:

01: 1st
02: 2nd
03: 3rd
04: 4th
05: 5th
06: 6th
07: 7th
08: 8th
09: 9th
10: 10th
11: 11th
12: 12th
13: 13th
14: 14th
15: 15th
16: 16th
17: 17th
18: 18th
19: 19th
20: 20th
21: 21st
22: 22nd
23: 23rd
24: 24th
25: 25th
26: 26th
27: 27th
28: 28th
29: 29th
30: 30th
31: 31st

[!NOTE] %D 说明符专门生成英文序数后缀(st, nd, rd, th),不受当前会话 lc_time_names 语言变量的影响。


总结

在 MariaDB 中生成带英文序数后缀的日期,只需在 DATE_FORMAT() 函数中使用 %D 说明符。如需查看 MariaDB 支持的全部日期时间格式符,请参阅 MariaDB 日期格式化说明符速查表。