From: | Daevor The Devoted <dollien(at)gmail(dot)com> |
---|---|
To: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
Cc: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: select date between - PostgreSQL 9.5 |
Date: | 2016-09-14 06:04:26 |
Message-ID: | CAAZnbVr9DEyTp2sqJKdC7zwSt77tH5RGnq9vOjwN4ssr8_BeAw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Sep 14, 2016 at 4:49 AM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
>
>
> 2016-09-14 13:17 GMT+12:00 David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>:
>
>> On 14 September 2016 at 12:20, Patrick B <patrickbakerbr(at)gmail(dot)com>
>> wrote:
>> > I want to select all rows that have been modified from now to 4 months
>> ago.
>> >
>> > I've used these queries:
>> >
>> >> select
>> >> modified_date,
>> >> from
>> >> clients
>> >> WHERE
>> >> modified_date BETWEEN '2016-06-13' AND '2016-09-13'
>> >
>>
>> Going by my clock here 2016-06-13 was just over 3 months ago, not 4.
>>
>>
>> >> select
>> >> modified_date,
>> >> from
>> >> clients
>> >> WHERE
>> >> modified_date >='2016-06-13' AND modified_date < '2016-09-13'
>> >
>> >
>> >
>> > But it didn't work... it returns 0 rows.... but there are rows to be
>> shown:
>> >
>> >
>> >> select modified_date from clients ORDER BY modified_date ASC
>> >
>> >
>> >
>> >> modified_date
>> >> -------------------
>> >> 2015-07-11 17:23:40
>> >> 2016-09-13 20:00:51
>> >> 2016-09-13 20:00:51
>> >> 2016-09-13 20:00:51
>> >> 2016-09-13 20:00:51
>> >
>> >
>> >
>> > What am I doing wrong?
>>
>> None of those dates are between your specified date range. If you want
>> to include all of 2016-09-13 timestamps, then you'd better do <
>> '2016-09-14' since < '2016-09-13' will only cover timestamps on the
>> 12th or before.
>>
>>
>> --
>> David Rowley http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
> Thanks guys...
>
> I've used < and >
>
> not sure why wasn't working before :(
>
> Thanks!
> Patrick
>
It didn't work before because, as excellently pointed out by Vitaly
Burovoy, because
modified_date BETWEEN '2016-06-13' AND '2016-09-13'
is evaluated as
modified_date >= '2016-06-13 00:00:00' AND modified_date <= '2016-09-13
00:00:00'
None of your timestamps falls in that range. '2016-09-13 20:00:51' is 20
hours and 51 seconds after the end of this range, and '2015-07-11 17:23:40'
is more than a year before it.
Similar logic applies to modified_date >= '2016-06-13 00:00:00' AND
modified_date
< '2016-09-13 00:00:00'
Now, the reason it is working for you now, is probably because you're in a
timezone where it is already 2016-09-14, and your WHERE clause now reads:
modified_date >= '2016-06-14 00:00:00' AND modified_date < '2016-09-14
00:00:00'
with the effect that the timestamp '2016-09-13 20:00:51' now falls within
the range of your new WHERE clause.
At least, that's my suspicion.
Kind regards,
Na-iem Dollie
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2016-09-14 08:06:51 | Re: Installing 9.6 RC on Ubuntu |
Previous Message | Adrian Klaver | 2016-09-14 03:23:48 | Re: Installing 9.6 RC on Ubuntu [Solved] |