List:General Discussion« Previous MessageNext Message »
From:Jim Ginn Date:March 18 2007 2:25pm
Subject:Re: Searching for Dates
View as plain text  
Dan:

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) ...

Jim

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

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