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

www.pocketpcheaven.com

[Gordon Burditt]

>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'
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.
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?
Given that you've got the date you need, extracting data
WHERE datestamp >= the_date_you_want
should be easy.

dayofweek(current_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(current_date()) - 4 + 7)%7 DAY)
The current month?
If today is the Nth day of something, we go back N-1 days to get
to the first.

subdate(current_date(), INTERVAL dayofmonth(current_date()) - 1 DAY)
Year?

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

concat(year(current_date()), '-01-01')

Gordon L. Burditt

[Bill Karwin]

toedipper wrote:

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?
If you need all dates in the current week, you could just compare the weeks:

WHERE WEEK(dateField) = WEEK(CURRENT_DATE())
The current month?
WHERE MONTH(dateField) = MONTH(CURRENT_DATE())
Year?

WHERE YEAR(dateField) = YEAR(CURRENT_DATE())

For more information on using these functions, see:
http://dev.mysql.com/doc/mysql/en/da...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.