> 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
>> 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
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1