Querying dates in mysql

[toedipper]

Hello,

MYsql and PHP

If I want to extract data with todays date then it's 'where blab blah =
current_date()'

For yesterday it's 'where blah blah = current_date()-1'

But can anyone tell me how to extract all data from the start of the
current week? Is there any way in Mysql to work out the start of the
current week?

The current month?

Year?

Thanks,

rg

https://www.pocketpcheaven.com

[Gordon Burditt]

Re: Querying dates in mysql

>MYsql and PHP[color=blue]
>
>If I want to extract data with todays date then it's 'where blab blah =
>current_date() '
>
>For yesterday it's 'where blah blah = current_date()-1'[/color]

I have serious doubts about the above working on the first day
of the month, especially if you use numbers larger than one.
For example, current_date()-30 appears to be August 83, 2005.

adddate() and subdate() are very useful functions.
[color=blue]
>But can anyone tell me how to extract all data from the start of the
>current week? Is there any way in Mysql to work out the start of the
>current week?[/color]

Given that you've got the date you need, extracting data
WHERE datestamp >= the_date_you_wa nt
should be easy.

dayofweek(curre nt_date())
is the current day of the week (1 = Sunday ... 7 = Saturday).
Now take the day number of whatever you think is the first day of
the week (there is *NOT* a universal consensus on this) and subtract.
Assuming you think Wednesday is the first day of the week (why not?),
that's 4. As is usual with day-of-week calculations, you need to
mod by 7 for wraparound.

The first day of the week (a Wednesday) is:
subdate(current _date(), INTERVAL (dayofweek(curr ent_date()) - 4 + 7)%7 DAY)
[color=blue]
>The current month?[/color]

If today is the Nth day of something, we go back N-1 days to get
to the first.

subdate(current _date(), INTERVAL dayofmonth(curr ent_date()) - 1 DAY)
[color=blue]
>Year?[/color]

This one I think I'll cheat on with the representation: get
the year and tack on January 1.

concat(year(cur rent_date()), '-01-01')

Gordon L. Burditt

[Bill Karwin]

Re: Querying dates in mysql

toedipper wrote:[color=blue]
> But can anyone tell me how to extract all data from the start of the
> current week? Is there any way in Mysql to work out the start of the
> current week?[/color]

If you need all dates in the current week, you could just compare the weeks:

WHERE WEEK(dateField) = WEEK(CURRENT_DA TE())
[color=blue]
> The current month?[/color]

WHERE MONTH(dateField ) = MONTH(CURRENT_D ATE())
[color=blue]
> Year?[/color]

WHERE YEAR(dateField) = YEAR(CURRENT_DA TE())

For more information on using these functions, see:

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

If you want to get only those dates from the beginning of the time
period to the current date, excluding dates after the current date, you
could append the following term to each of the above examples:

AND dateField <= CURRENT_DATE()

Regards,
Bill K.