On Wed, Sep 14, 2016 at 4:49 AM, Patrick B <patrickbake...@gmail.com> wrote:
> > > 2016-09-14 13:17 GMT+12:00 David Rowley <david.row...@2ndquadrant.com>: > >> On 14 September 2016 at 12:20, Patrick B <patrickbake...@gmail.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
