Postgres:查找日期,包括日期本身 [英] Postgres: find date in between including the date itself
问题描述
我需要在某个日期之间找到行,包括日期本身。
我目前使用的是:
SELECT x,y,z FROM table WHERE dateandtime between
DATE('。date('Ymd H:i:s',strtotime($ from))。'')和
DATE ''.date('Ymd H:i:s',strtotime($ to))。'')
但这只显示了之间的日期,所以如果从15/02/2015 - 15/02/2015选择:
我没有看到任何东西,而不是看到第15天。
如果我这样做,同样的事情:
从14/02/2015 - 15/02/2015。
我看不到第15个但只有第14个。
所以我的解决方法是检查PHP如果 code>和
到
日期是一样的,然后才发现那一天。但是这并不是一个很好的解决方案,我仍然有第二部分的问题。
根据要求进行编辑
这是生成的查询:
SELECT x,y,z FROM table WHERE dateandtime between
/ pre>
DATE('2015-01-15 00:00:00')和
DATE('2015-01-15 00:00:00')
所以我添加了一个if:
SELECT x, y,z FROM table WHERE dateandtime BETWEEN
'.date('Ymd H:i:s',strtotime($ from。'00:00:00'))'AND
' .date('Ymd H:i:s',strtotime($ from。'23:59:59'))'
这是结果:
SELECT x,y,z FROM table WHERE dateandtime BETWEEN
'2015-01-15 00:00:00'AND
'2015-01-15 23:59:59'
我有同样的问题,因为只有当
从
和到
是一样的
添加sqlfiddle
正如你可以看到没有采取的第五个jannuary: http://sqlfiddle.com/# !9 / d50bd / 1
解决方案最简单的解决方案是使用
< =
和> =
运算符SELECT x,y,z
FROM table
WHERE DATE('。date('Ymd H:i:s',strtotime($ from))。'')< = dateandtime
AND dateandtime< = DATE('。date('Ymd H:i:s',strtotime($ to))。'')
I have a Posgres table with dateandtime column (YYYY-MM-DD HH:MM:SS format).
I need to find the rows between a certain date, including the date itself.
I'm currently using this:
SELECT x,y,z FROM table WHERE dateandtime between DATE('".date('Y-m-d H:i:s',strtotime($from))."') and DATE('".date('Y-m-d H:i:s',strtotime($to))."')
but this shows only the date in between, so that if I select:
from 15/02/2015 - to 15/02/2015. I don't see anything, instead of seeing the day 15th.
Same thing if I do this:
from 14/02/2015 - to 15/02/2015. I don't see the 15th but only the 14th.
So my workaround was to check in PHP if the
from
andto
date are the same, then find that day only. But this is not a good solution and I still have the second part of the problem.EDIT as requested
This is the generated query:
SELECT x,y,z FROM table WHERE dateandtime between DATE('2015-01-15 00:00:00') and DATE('2015-01-15 00:00:00')
So I added an if:
SELECT x,y,z FROM table WHERE dateandtime BETWEEN '".date('Y-m-d H:i:s',strtotime($from.' 00:00:00'))."' AND '".date('Y-m-d H:i:s',strtotime($from.' 23:59:59'))."'
And this is the result:
SELECT x,y,z FROM table WHERE dateandtime BETWEEN '2015-01-15 00:00:00' AND '2015-01-15 23:59:59'
I have the same problem because the if checks only if the
from
andto
are the same. If they are not I still have one day outside the scope.Added sqlfiddle
As you can see the 5th of jannuary is not taken: http://sqlfiddle.com/#!9/d50bd/1
解决方案The easiest solution to your problem is using the
<=
and>=
operatorsSELECT x,y,z FROM table WHERE DATE('".date('Y-m-d H:i:s',strtotime($from))."') <= dateandtime AND dateandtime <= DATE('".date('Y-m-d H:i:s',strtotime($to))."')
这篇关于Postgres:查找日期,包括日期本身的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!