MySQL 在一个查询中修剪和字符串到日期

Question

我正在尝试根据日期搜索一些 vchar 值。

我必须使用的字符串如下所示:

2006年1月7日上午12:45:24

2006年1月7日上午1:18:36

2006年1月7日上午1:21:43

2006年1月7日上午1:32:09

2006年3月30日下午12:32:10

2006年3月30日下午1:19:30

2006年3月30日下午1:20:44

所以首先让我们摆脱 AM.. PHPMyAdmin 的 sql 查询是:

 SELECT  trim('AM'  FROM  `orderdate`) FROM tblorders

这可以消除 AM，现在让我们将值设置为变量，并尝试将字符串包装到 str_to_date() 的结果周围:

SELECT  trim('AM'  FROM  `orderdate`) AS `value`, STR_TO_DATE(`value`,'%d,%m,%Y') FROM tblorders

这会产生未知列的值。您还如何将两个函数值串在一起以便使用它们进行过滤，例如 WHERE value > 2/1/2006 ？

Answer 1

您可以编写如下函数:

select str_to_date(trim('AM' from orderdate), '%m/%d/%Y')

请注意，我还更正了您的日期格式以匹配您的数据。您实际上不需要修剪这些值来对它们使用 str_to_date，这样就可以正常工作:

select str_to_date(orderdate, '%m/%d/%Y')

如果您想在 WHERE 子句中使用它，请将函数调用放在那里:

select trim('AM' from orderdate), str_to_date(orderdate, '%m/%d/%Y')
from your_table
where str_to_date(orderdate, '%m/%d/%Y') > '2006-01-02'

并让优化器识别重复或使用派生表:

select value, the_date
from (
    select trim('AM' from orderdate) as value, str_to_date(orderdate, '%m/%d/%Y') as the_date
    from your_table
) dt
where the_date > '2006-01-02'

注意 ISO 8601 的使用查询中的日期，该格式是明确的，任何值得使用的数据库都会理解它，无论您的区域设置如何。

我还建议您修复架构以使用真实时间戳而不是那些字符串。