List:General Discussion« Previous MessageNext Message »
From:Bob Cooper Date:March 20 2007 3:18pm
Subject:Re: Searching for Dates
View as plain text  
To you all,

Thanks for your input. The "TO_DAYS(date_col)%4 = TO_DAYS('2006-4-17')%
4" where statement worked perfectly. And, thanks for the reference to
the manual. It can be a bit daunting for a newbie.

Bob Cooper


> Dan:
> 
> Thanks!
> 
> Jim
> 
> > In the last episode (Mar 18), Jim Ginn said:
> >> > In the last episode (Mar 16), Bob Cooper said:
> >> >> I am working with MySQL ver 5.1 on a Ubuntu Linux x86_64. I am
> new
> >> >> to both SQL and MySQL. I have been able to query out most of the
> >> >> data I need from my tables without any issues but his one has
> >> >> stumped me.
> >> >>
> >> >> I am trying to query data associated with specific dates. The
> >> >> dates are not sequential but somewhat sporadic. I would like to
> >> >> query out data/dates that are every 4 days from a starting date.
> >> >>
> >> >> 2006-4-17, 2006-4-21, etc.
> >> >>
> >> >> I have tried ADDDATE('2006-4-14',interval 4 day)<=Date_col but
> it
> >> >> give me only the next date 2006-4-21.
> >> >
> >> > You could do it b
> 
> y converting to a daynumber (the number of days
> >> > since year 0) and doing modulo arithmetic:
> >> >
> >> > WHERE TO_DAYS(date_col)%4 = TO_DAYS('2006-4-17')%4
> >> >
> >> >
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_to-days
> >>
> >> I've used the TO_DAYS on a SELECT statement ie:
> >>
> >> SELECT * FROM properties WHERE
> >> (TO_DAYS(NOW()) - TO_DAYS(CreationDate) = 1) ORDER BY id DESC
> >>
> >> however it didn't seem to take advantage or use the index on that
> field
> >> (ie. CreationDate) ...
> >
> > Right; mysql needs CreationDate all by itself on one side of a
> > comparison operator to be able to use an index.  In your case, try
> >
> >   WHERE CreationDate = CURDATE() - INTERVAL 1 DAY
> >
> > , assuming CreationDate is a 'date' field type.  If it's a datetime,
> > you'll need to use a BETWEEN operator and cover the time range from
> > midnight to midnight on your target day.
> >
> > --
> >       Dan Nelson
> >       dnelson@stripped
> >
Thread
Searching for DatesBob Cooper17 Mar
  • Re: Searching for DatesDan Nelson18 Mar
    • Re: Searching for DatesJim Ginn18 Mar
      • Re: Searching for DatesDan Nelson18 Mar
        • Re: Searching for DatesJim Ginn18 Mar
Re: Searching for DatesBob Cooper20 Mar