MYSQL 从可用日期范围中选择最接近当前日期的日期

发布于 2024-11-29 03:13:00 字数 567 浏览 0 评论 0原文

我有一个关于 MYSQL 查询的问题，以及如何处理获取与当前日期最接近的日期范围...

所以我有 id、from_date、until_date 形式的记录。

给定当前日期，我想选择不仅包含当前日期的记录，而且如果有多个记录满足要求，则选择其起始日期和截止日期最接近当前日期的记录，例如例如：

if:

record1: from_date1, until_date1
record2: from_date2, until_date2

from_date1 <= current_date <= until_date1
from_date2 <= current_date <= until_date2

from_date1 >= from_date2 and until_date1 <= until_date2

record1 应该被选择...

我正在寻找一个选择查询语法来实现这个...

我希望我在我的例子中足够清楚...任何帮助都会不胜感激！

谢谢！ :)

彼得。

原文

I've a question regarding MYSQL queries, and how I could go about handling getting the closest date range to the current date...

So I have records in the form of id, from_date, until_date.

Given the current date, I would like to select the record that not only includes the current date, but if there is more than one record which meets the requirement, that the one which has its from and until dates closest to the current date, such that for example:

if:

record1: from_date1, until_date1
record2: from_date2, until_date2

from_date1 <= current_date <= until_date1
from_date2 <= current_date <= until_date2

from_date1 >= from_date2 and until_date1 <= until_date2

record1 should be chosen...

I am looking for a select query syntax to achieve this...

I hope I am being clear enough in my example... any help would be greatly appreciated!

Thanks! :)

Piotr.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

网名女生简单气质 2024-12-06 03:13:00

SELECT from_date, until_date
    FROM table
    WHERE current_date BETWEEN from_date
        AND until_date
    ORDER BY TIMESTAMPDIFF(SECOND, from_date, until_date)
    LIMIT 1

选择所有符合条件的记录；最后一个没有 100% 遵守 - 如果 from_date2 非常接近 current_date，但 until_date2 太远以至于时间跨度为更大？

SELECT from_date, until_date
    FROM table
    WHERE current_date BETWEEN from_date
        AND until_date
    ORDER BY TIMESTAMPDIFF(SECOND, from_date, until_date)
    LIMIT 1

selects all records who match the condition; the last one is not obeyed to 100% - what if from_date2 is very close to current_date, but until_date2 so far away that the time span is larger?

回复收藏 0 原文

~没有更多了~