time based different mysql queries

please help to enhance the logic if possible

[phparion]

Hi

I have come up with the following queries which are working for me. Please see it and let me know if i can enhance the logic

Only today's records

WHERE visiTime < date_sub(curdate(),interval 0 day)

Only Yesterday and not today

WHERE visiTime BETWEEN date_sub(curdate(),interval 1 day) AND date_sub(curdate(),interval 0 day)

This Week starting from Monday to current weekday

WHERE visiTime BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY ) AND CURDATE( )

Only this month from its starting day, NOT 30 days from today.

where visiTime
>= date_sub(current_date
, interval dayofmonth(current_date)-1 day)
and visiTime
< date_add(
date_sub(current_date
, interval dayofmonth(current_date)-1 day)
, interval 1 month)

Only last month and NOT THIS MONTH

WHERE month(visiTime) = month(date_add(now(),interval -1 month))

Only last year and NOT THIS YEAR

WHERE year(visiTime) = year(date_add(now(),interval -1 year))

I know expert mysql admins are very busy people but please please share your thoughts on this. thank you

[coopster]

I'll start with the first one!

Only today's records:

WHERE visiTime = CURRENT_DATE

[rocknbil]

current_date() is synonymous with curdate(); :-)

A couple comments:
If you want a future date, use date_add. A past date, date_sub. When you do date_add(curdate(),-1 year) you get into a double-negative confusion. :-)

Second, curdate() is a date only format:
0000-00-00
now() is a datetime format:
0000-00-00 00:00:00

Although the date math will work on either, I always say "right tool for the job." If you don't need a time, use curdate().

Only today's records

WHERE visiTime = curdate();

Only Yesterday and not today

WHERE visiTime = date_sub(curdate(),interval 1 day);

The rest I don't have time for this AM, others will assist!

[coopster]

current_date() is synonymous with curdate(); :-)

In MySQL, yes, but the standard is CURRENT_DATE (no parenthesis). It is a "niladic" or "nullary" datetime function. I believe CURDATE originated with ODBC and was replaceable with standard SQL3. Anyway, the point I was making was that there was no need for the mathematical expression using an interval when a comparison expression would suffice.

To take it a step further, why not write all the clauses using standard SQL rather than MySQL-specific functions? Makes the code portable ... just a thought.

[phparion]

thank you for the replies. there is some problems, logical, in my queries; told by another mysql admin. e.g

WHERE month(visiTime) = month(date_add(now(),interval -1 month))

this will return the records of april 2005, april 2006, april 2008 april 2009 and so on.

i am not sure if he is right or not and unfortunately i didn't get solution from him.

[coopster]

Well, yes, that is correct. You are only looking at the month in this particular case. I assume you are referring to this query:

Only last month and NOT THIS MONTH

WHERE month(visiTime) = month(date_add(now(),interval -1 month))

If so, you need to add the year to that query. Compare year/month rather than just month.