返回下个月的行，MYSQL [英] Return rows for next month, MYSQL

问题描述

我有一个 mysql 表，用于存储用户的可用性，作为日期字段存储在开始"和结束"列中.

I have a mysql table which stores users' availability, stored in 'start' and 'end' columns as date fields.

我有一个表单，其他用户可以在其中搜索不同时期的可用性"，例如今天、明天和下周.我试图弄清楚如何构建查询以获取下个月"可用的用户的所有行.

I have a form where other users can search through the 'availabilty' with various periods like, today, tomorrow and next week . I'm trying to figure out how to construct the query to get all the rows for users who are available 'next month'.

开始"值可能从今天开始，结束"值可能是三个月后的值，但如果下个月介于开始"和结束"之间，那么我希望返回该行.我能得到的最接近的是下面的查询，但它只返回开始"在下个月内的行.非常感谢，

The 'start' values maybe from today and the 'end' value might might be three months away but if next month falls between 'start' and 'end' then I would want that row returned. The nearest I can get is with the query below but that just returns rows where 'start' falls within next month. Many thanks,

sql= "SELECT * FROM mytable WHERE start BETWEEN DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)),INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AND LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH))";

推荐答案

如果您对当前日期之后整月内发生的任何事情感兴趣，您可以尝试以下操作:

As you are interested in anything that happens in the full month following the current date you could try something like this:

SELECT * FROM mytable WHERE
FLOOR(start/100000000)<=FLOOR(NOW()/100000000)+1 AND
FLOOR(  end/100000000)>=FLOOR(NOW()/100000000)+1

这个查询利用了这样一个事实:datetime 值在 MySql 内部存储为像

This query make use of the fact that datetime values are stored in MySql internally as a number like

SELECT now()+0
--> 20150906130640

其中数字 09 指的是当前月份.FLOOR(NOW()/100000000) 过滤掉号码的前几位(在本例中:201509).WHERE 条件现在简单地测试 start 日期是否在下个月结束之前 和 end 之前的任何位置日期至少在下个月的期间或之后.

where the digits 09 refer to the current month. FLOOR(NOW()/100000000) filters out the first digits of the number (in this case:201509). The WHERE conditions now simply test whether the start date is anywhere before the end of the next month and the end date is at least in or after the period of the next month.

(在我的版本中，我特意忽略了 start 需要在今天之后"的条件，因为在我看来，更早开始的时期仍然适用于您所描述的目的.如果，但是，您也希望包含该条件，您只需在 WHERE 子句的末尾添加一个 AND start > now().)

(In my version I purposely left out the condition that start needs to be "after today", since a period that has started earlier seems in my eyes still applicable for your described purpose. If, however, you wanted that condition included too you could simply add an AND start > now() at the end of your WHERE clause.)

编辑

由于您的 SQLfiddle 是使用 date 而不是(正如我假设的)datetime 列设置的，因此您的日期将以不同的数字格式表示，例如 20150907 和一个简单的除以 100 现在将为您提供所需的月份数进行比较(201509):

As your SQLfiddle is set-up with a date instead of a (as I was assuming) datetime column your dates will be represented differently in mumeric format like 20150907 and a simple division by 100 will now get you the desired month-number for comparison (201509):

SELECT * FROM mytable WHERE  
FLOOR(start/100)<=FLOOR(NOW()/100000000)+1 AND
FLOOR(  end/100)>=FLOOR(NOW()/100000000)+1

NOW()返回的数字仍然是14位数字，需要除以100000000.在此处查看更新的小提琴:SQLfiddle

The number returned by NOW() is still a 14-digit figure and needs to be divided by 100000000. See your updated fiddle here: SQLfiddle

我还添加了另一个不符合您要求的记录(查理").

I also added another record ('Charlie') which does not fulfill your requirements.

更新

为了更好地适应年份变化的情况，我更新了我的 SqlFiddle.where 子句现在基于 12*YEAR(..)+MONTH(..) 类型函数.

To better accommodate change-of-year scenarios I updated my SqlFiddle. The where clause is now based on 12*YEAR(..)+MONTH(..) type functions.

这篇关于返回下个月的行，MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！