[UPHPU] select records with date

[justin]

s/DATE_DIFF/DATEDIFF/

sorry 'bout that :)


On Thu, Jan 28, 2010 at 9:11 AM, justin <justin at justinhileman.info> wrote:
> On Thu, Jan 28, 2010 at 4:37 AM, Wade Preston Shearer
> <wadeshearer.lists at me.com> wrote:
>> I have a difficult MySQL query to write. Is there anyone that can assist?
>>
>> I have a users table. I have another table with things the user does.
>>
>> I need to select all the things the user has done within the current three
>> year window since the date the user's account was created.
>>
>>
>> Example:
>>
>> So, if the user's account was created in Feb 2001, they did something in Mar
>> 2003, something in Nov 2009, and something else in Jan 2010, the query would
>> return 2 records, because they did…
>>
>> .       one thing in the first three years
>> .       nothing in the second three years
>> .       and two things in the third (current) three year period
>>
>>
>> …and we only want the things that were done within the current three year
>> period.
>>
>>
>> Is that possible in a single query?
>>
>
> If I read it correctly, it sounds like this is your logic:
>
> # number of days since last 3 yr mark.
> x = (current date - hire date) % (365 days * 3)
>
> # slice you care about
> select everything between (current date - x) and current date
>
>
> MySQL would look something like this (disclaimer: i didn't actually
> run this, but it looks right):
>
>
> SELECT a.*
> FROM users u
> LEFT JOIN user_actions a
> ON a.user_id = u.id
> WHERE
>        u.id = ?
> AND
>        DATE(a.created_at)
>                BETWEEN
>                        DATE_SUB(
>                                CURDATE(),
>                                MOD(
>                                        DATE_DIFF(
>                                                CURDATE(),
>                                                DATE(u.created_at)
>                                        ),
>                                        (365 * 3)
>                                ) DAYS
>                        )
>                AND
>                        CURDATE()
>
>
>
> --
> justin
> http://justinhileman.com
>



-- 
justin
http://justinhileman.com