英文:
MySQL to return records with a date/time of now minus 1 hour?
问题
以下是已翻译的内容：
"能否帮助使用MySQL命令尝试选择表中日期/时间等于或大于现在减去1小时的所有记录？
现在我不是100%确定这是否是描述这个问题的最佳方式。
我基本上有一个带有日期/时间字段的记录（例如2019-07-13 13:00:00），我想执行MySQL选择以查找所有日期/时间为一小时前的记录。这是为了在事件发生后一小时触发一个函数。
我目前有这个，但不确定是否正确：
SELECT * FROM database.table_name
WHERE (NOW() - INTERVAL 1 HOUR) >= 'date_of_event'
AND 'status' = 'Scheduled';
任何想法都将很棒！"
英文:
can anybody help with a MySQL command to try and select all records within a table with a date/time equal to or more than now - 1hour?
Now I'm not 100% sure that that is the best way of describing this.
I basically have records with a date/time field (e.g. 2019-07-13 13:00:00) and I want to perform a MySQL select to find all records with a date/time of one hour ago. This is to trigger a function one hour after an event.
I currently have this, but not sure if it is along the right lines at all:
SELECT * FROM database.table_name
WHERE (NOW() - INTERVAL 1 HOUR) >= 'date_of_event'
AND 'status' = 'Scheduled';
Any thoughts would be great!
答案1
得分: 17
你离答案非常接近。在我看来，编写涉及日期/时间的WHERE条件的最可读方式是：
WHERE date_of_event >= NOW() - INTERVAL 1 HOUR
为什么呢？>= 对于日期/时间值意味着在或之后。您需要能够查看您的查询代码并进行推理。这种表达方式清楚地符合您的规范：
一个日期/时间等于或超过现在-1小时？
但是：您说您想在一小时后触发事件。这可能意味着您想选择
一个一小时或更早之前的日期/时间，即一个日期/时间等于或小于现在-1小时。
这就是您已经有的内容。但我会重写它为：
WHERE date_of_event <= NOW() - INTERVAL 1 HOUR
<= 意味着在或之前。
专业建议 除非您的列或表与保留字（如 SELECT 或 GROUP）具有相同的名称，否则避免使用反引号。避免使用保留字作为列或表的名称。反引号看起来非常像单引号，很容易混淆。
专业建议 具有= 的日期/时间表达式几乎永远不会为真，因为相等性必须精确到秒或毫秒。因此，避免类似 date_of_event = NOW() - INTERVAL 1 HOUR 这样的条件。
英文:
You are very close. The most readable way, in my opinion, to write WHERE conditions involving date / times is:
WHERE date_of_event >= NOW() - INTERVAL 1 HOUR
Why? >= for date/time values means on or after. You need to be able to look at your query code and reason about it. That formulation clearly matches your specification:
> a date/time equal to or more than now - 1hour?
But: you say you want to trigger an event one hour after the time. That probably means you want to choose
> a date/time one hour or more ago, that is a date/time equal to or less than now - 1 hour.
That's what you have already. But I would rewrite it
WHERE date_of_event <= NOW() - INTERVAL 1 HOUR
<= means on or before.
Pro tip Avoid backticks unless your columns or tables have the same names as reserved words like SELECT or GROUP. Avoid naming your columns or tables with reserved words. Backticks look so much like single-quotes that it's easy to get confused.
Pro tip date/time expressions with = in them almost never come up true, because the equality must be exact, down to the second or millisecond. So avoid conditions like date_of_event = NOW() - INTERVAL 1 HOUR.
答案2
得分: 2
SELECT * FROM database.table_name
WHERE date_of_event >= NOW() - INTERVAL 1 HOUR
AND status = 'Scheduled';
英文:
You can use:
SELECT * FROM database.table_name
WHERE `date_of_event` >= NOW() - INTERVAL 1 HOUR
AND `status` = 'Scheduled';
Note that you use backticks instead of single quotation marks on reserved words.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论