如何使用COALESCE处理不同日期类型获取最小非空日期

嘿，我注意到你想要从date1(timestamp)、date2(date)、date3(timestamp)这三个字段里取最小的非NULL日期，如果所有字段都是NULL就用NOW()。先给你分析下你的现有语句，再给出符合需求的正确写法：

你的现有语句分析 #

你写的查询：

SELECT coalesce(date1, timestamp(date2), date3, now()) as edited FROM backupDB

这个语句的作用是返回第一个非NULL的参数，而非所有非NULL值中的最小值：

• 在你的示例数据（date1、date2为NULL，date3为2015-02-04 21:29:05）下，它会正确返回date3的值，这没问题；
• 但如果多个字段都有值（比如date1是2020-01-01，date3是2015-02-04），它会返回date1（第一个非NULL），而不是更小的date3，这就不符合“取最小日期”的需求了。

正确实现：获取最小非NULL日期 #

要实现“取所有非NULL日期中的最小值”，需要结合LEAST和COALESCE——因为LEAST只要有一个参数是NULL就会返回NULL，所以我们需要先把每个字段的NULL替换成一个远未来的时间（确保它不会成为最小值），再用LEAST取最小，最后判断如果所有字段都是NULL，就返回NOW()。

PostgreSQL版本 #

SELECT COALESCE(
  LEAST(
    COALESCE(date1, '9999-12-31 23:59:59'::TIMESTAMP),
    COALESCE(date2::TIMESTAMP, '9999-12-31 23:59:59'::TIMESTAMP),
    COALESCE(date3, '9999-12-31 23:59:59'::TIMESTAMP)
  ),
  NOW()
) AS edited FROM backupDB;

MySQL版本 #

SELECT COALESCE(
  LEAST(
    COALESCE(date1, '9999-12-31 23:59:59'),
    COALESCE(CAST(date2 AS TIMESTAMP), '9999-12-31 23:59:59'),
    COALESCE(date3, '9999-12-31 23:59:59')
  ),
  NOW()
) AS edited FROM backupDB;

逻辑解释 #

• 内层COALESCE：把每个字段的NULL替换成9999-12-31 23:59:59（一个不会成为最小值的远未来时间）；
• LEAST：从处理后的三个值中取最小的，也就是实际存在的最小非NULL日期；
• 外层COALESCE：如果LEAST返回的是那个远未来时间（说明所有字段都是NULL），就返回NOW()，否则返回最小日期。

如果你的需求就是取第一个非NULL值 #

那你的现有语句是可行的，只是可以把timestamp(date2)换成更简洁的类型转换写法：

• PostgreSQL：date2::TIMESTAMP
• MySQL：CAST(date2 AS TIMESTAMP)

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