表日期列与生成日期列表的比较 - MYSQL [英] Table Date column comparison with a generated dates list - MYSQL

问题描述

比较表列中的日期和MYSQL中列表中生成的日期似乎有一种奇怪的行为。

请查看 * SQLFIDDLE 参考。

工资表：：

  ID DATESTAMP 
 2012年8月1日00：00：00 + 0000 
 2012年9月2日，2012年9月2日00：00：00 + 0000 
 3月9日，15 2012 00：00：00 + 0000 
 2012年9月4日00：00：00 + 0000 
 5月5日，05 2012 00：00：00 + 0000 
 6月6日，2012年12月00：00：00 + 0000 
 2012年10月7日00：00：00 + 0000 
 2012年11月8日00：00：00 + 0000 
 2012年11月9日10月00日00：00：00 + 0000 
 2012年11月10日00：00：00 + 0000 
 11月11日，24 2012 00：00：00 + 0000 
 2012年11月12日12月30日00：00：00 + 0000 
 2012年12月13日00：00：00 + 0000 
 2012 00：00：00 + 0000 
  

日期列表在两个特定日期之间生成

查询：

  set @ i：= 0; 
 
 SELECT date_format（DATE（ADDDATE（'2012-10-05'，
 INTERVAL @i：= @ i + 14 DAY）），'％Y-％m-％d' ）
 AS dateP，@i 
 FROM payroll 
 HAVING @i< datediff（now（），date'2012-10-05'）
; 
 
 DATEP @IntervalDays 
 2012-10-19 14 
 2012-11-02 28 
 2012-11-16 42 
 2012-11-30 56 
 2012-12-14 70 
  

您可以看到生成的日期列表有匹配到上面的工资表。

pre> set @i：= 0;

SELECT distinct datestamp from payroll
WHERE date（datestamp）in（
SELECT DATE（ADDDATE（'2012-10-05'，
INTERVAL @i：= @ i + 14 DAY））AS dateP
FROM payroll
其中@i< DATEDIFF（now（），date'2012-10-05'）
）
;


所以我有问题：

• 内部查询在用作嵌套查询时是否停止生成日期？






• 这整个失败的原因是什么？




• 如何修复选择本身没有任何程序/功能？ :)

PS：

在SQL服务器以及Oracle中进行测试。

有很多好的问题和答案支持日期比较问题发生在各种情况下与网站。包括诸如 mysql日期与date_format 之间的比较等帖子。可能有一个隐藏在某处要求我用不同的措辞面对的确切问题。

解决方案

第二个UPDATE： b
$ b

现在我可以在每个版本中使用它：

  select 
 * 
从
工资
内部连接
（
 SELECT DATE（DATE_ADD（'2012-10-05'，
 INTERVAL @i：= @ i + 14 DAY））AS dateP 
 FROM Payroll（SELECT @i：= 0）r 
其中@i ） sq on Payroll.datestamp = sq.dateP 
  

您只需在查询中初始化变量。 / p>

UPDATE：

奇怪的是，这个在我的本地计算机

 设置@i：= 0（<版本5.1.41-3ubuntu12.7-日志），但不在您的SQLfiddle中。 ; 
 select 
 * 
从
工资
内部连接
（
 SELECT DATE（DATE_ADD（'2012-10-05'，
 INTERVAL @i：= @ i + 14 DAY））AS dateP 
 FROM Payroll 
其中@i  ）sq on Payroll.datestamp = sq.dateP 
  

END OF UPDATE / p>

您是否尝试过这样？

  set @i： 0; 
 
 SELECT distinct datestamp FROM payroll 
 WHERE STR_TO_DATE（datestamp，'％M，％d％Y％H：％i：％f'）in（
 SELECT DATE（ADDDATE （'2012-10-05'，
 INTERVAL @i：= @ i + 14 DAY））AS dateP 
 FROM payroll 
其中@i< DATEDIFF 2012-10-05'）
）
; 
  

我的猜测是 DATE（）函数失败，因为你 varchar （是吗？）日期不是ISO格式。因此，您必须使用 STR_TO_DATE（）函数。

完全使用 STR_TO_DATE ）阅读此处和这里。我不知道微秒部分。

There seem to be a weird behaviour when comparing a date in table column with a date generated in a list within MYSQL.

Please take a look at the * SQLFIDDLE reference.

Payroll Table:

ID  DATESTAMP
1   August, 30 2012 00:00:00+0000
2   September, 02 2012 00:00:00+0000
3   September, 15 2012 00:00:00+0000
4   September, 24 2012 00:00:00+0000
5   October, 05 2012 00:00:00+0000
6   October, 16 2012 00:00:00+0000
7   October, 19 2012 00:00:00+0000
8   November, 02 2012 00:00:00+0000
9   November, 10 2012 00:00:00+0000
10  November, 16 2012 00:00:00+0000
11  November, 24 2012 00:00:00+0000
12  November, 30 2012 00:00:00+0000
13  December, 01 2012 00:00:00+0000
14  December, 07 2012 00:00:00+0000

Dates list is generated between two particular dates with a constant day interval

Query:

set @i:= 0;

SELECT date_format(DATE(ADDDATE('2012-10-05', 
INTERVAL @i:=@i+14 DAY)),'%Y-%m-%d')
AS dateP, @i
FROM payroll
HAVING @i < datediff(now(), date '2012-10-05')
;

DATEP          @IntervalDays
2012-10-19     14
2012-11-02     28
2012-11-16     42
2012-11-30     56
2012-12-14     70

As you can see the generated dates list has matches to the Payroll table above. However when the comparison is done, it reutns zero records.

Comparison Query:

set @i:= 0;

SELECT distinct datestamp FROM payroll
WHERE date(datestamp) in (
SELECT DATE(ADDDATE('2012-10-05', 
INTERVAL @i:=@i+14 DAY) ) AS dateP
FROM payroll
where @i < DATEDIFF(now(), date '2012-10-05') 
)
;

So Questions I have:

• Is the inner query stop generating dates when used as a nested query?

• Is there anything wrong with the dates comparison method I am using here?

• What could be the reason for this entire failure?

• How to fix it within Select itself without any procedure/functions? :)

PS:

I am also trying to test this in SQL server as well as Oracle.

There are many good questions and answers to support 'Date Comparison' issues occurred at various scenarios with the site. That includes posts such as mysql date comparison with date_format. etc.. May be there's one hidden somewhere asking for exact issue I am facing with different wording. Couldn't find and hence posted the question.

解决方案

Second UPDATE:

Now I got it working in every version:

select
*
from
Payroll
inner join 
(
SELECT DATE(DATE_ADD('2012-10-05', 
INTERVAL @i:=@i+14 DAY) ) AS dateP
FROM Payroll, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date '2012-10-05') 
) sq on Payroll.datestamp = sq.dateP

You just have to initialize the variable inside the query.

UPDATE:

Strange thing is, this one works on my local machine without problems (version 5.1.41-3ubuntu12.7-log), but not in your SQLfiddle.

set @i:= 0;
select
*
from
Payroll
inner join 
(
SELECT DATE(DATE_ADD('2012-10-05', 
INTERVAL @i:=@i+14 DAY) ) AS dateP
FROM Payroll
where @i < DATEDIFF(now(), date '2012-10-05') 
) sq on Payroll.datestamp = sq.dateP

END OF UPDATE

Have you tried it like this?

set @i:= 0;

SELECT distinct datestamp FROM payroll
WHERE STR_TO_DATE(datestamp, '%M, %d %Y %H:%i:%f') in (
SELECT DATE(ADDDATE('2012-10-05', 
INTERVAL @i:=@i+14 DAY) ) AS dateP
FROM payroll
where @i < DATEDIFF(now(), date '2012-10-05') 
)
;

My guess is, that the DATE() function fails, cause you're varchar(is it?) date is not in ISO format. Therefore you have to use STR_TO_DATE() function.

For exact usage of STR_TO_DATE() read here and here. I'm not sure about the microsecond part.

这篇关于表日期列与生成日期列表的比较 - MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！