英文:
Why is mysql date_format function returning a result 45 minutes different from the input time?
问题
我在RDS上使用的是MySQL 5.7，并执行了以下查询。输入和输出之间有45分钟的时间差。我想知道为什么我得到了错误的结果。
select css_regDate
from j2t_counsel_sms
where date_format(css_regDate, '%Y-%m-%d %H:%m:%s') = '2022-02-18 09:02:53';
我得到的结果如下：
css_regDate
'2022-02-18 09:47:53'
英文:
I'm using mysql 5.7 on RDS and I executed query.
there is 45 mins difference between input and output. I want to know why I got the wrong result.
select css_regDate
from j2t_counsel_sms
where date_format(css_regDate, '%Y-%m-%d %H:%m:%s') = '2022-02-18 09:02:53';
and I got result like below
css_regDate
'2022-02-18 09:47:53'
答案1
得分: 3
你的日期格式有误。对于日期时间2022-02-18 09:47:53，使用格式'%Y-%m-%d %H:%m:%s'将其转换为2022-02-18 09:02:53。你在月份和分钟上都使用了%m，而应该使用%i代表分钟。你的最终语句应该如下所示：
SELECT css_regDate
FROM j2t_counsel_sms
WHERE DATE_FORMAT(css_regDate, '%Y-%m-%d %H:%i:%s') = '2022-02-18 09:02:53';
有关格式详情的完整列表，你可以查看MYSQL DATE_FORMAT文档中的表格。
| 指示符 | 描述 |
|---|---|
| %i | 分钟，数字表示（00..59） |
英文:
Your date format is wrong. With the datetime of 2022-02-18 09:47:53, a format of '%Y-%m-%d %H:%m:%s' would translate it to 2022-02-18 09:02:53. You are using %m for both month and minute, whereas you should be using %i for minute instead. Your final statement should be along the lines of:
SELECT css_regDate
FROM j2t_counsel_sms
WHERE DATE_FORMAT(css_regDate, '%Y-%m-%d %H:%i:%s') = '2022-02-18 09:02:53';
For a full list of the format details, you can look at the MYSQL DATE_FORMAT documentation's table. For this instance:
> |Specifier| Description|
> |-|-|
> |%i |Minutes, numeric (00..59)|
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论