MySQL date/time queries question

Can't figure out how to write a query to select records with todays date.

[Soupisgoodfood]

Hi. I'm having a bit of trouble trying to figure out how to use date calculations with MySQL. I recently figured out to select a record within 10mins of the current time (...WHERE downloaded > SUBDATE(CURRENT_TIMESTAMP, INTERVAL 10 MINUETS)...). But I still havn't got my head around how the date/time works, dispite looking over the stuff at mysql.com .

I have the following query:

SELECT
*
FROM
downloads
WHERE
DATE(downloaded) = CURRENT_DATE;

In other words: I want to select all from downloads from a datetime column called downloaded for today.

Unfortunatly, this query gives an error. I replaced 'DATE' with 'YEAR', 'MONTH', and 'DAYOFMONTH'. And get not errors, but this isn't what I want. And I can't figure out how to use the whole date.

I also need to create queries for selecting records from the day before, the current week, previous week, current month and previous month. Would these use a similar equation to the on above?

Thanks,
Justin.

[Lisa]

Here you go....

SELECT
*
FROM
downloads
WHERE
downloaded = CURRENT_DATE;

Mysql Date functions [mysql.com]

[Soupisgoodfood]

Thanks, but I already tired that, and it returns an empty set, I think that because column downloads is a timestamp, and not just a date. Although, I though that MySQL could handel that?

[lorax]

Hello Soupisgoodfood,

I think that because column downloads is a timestamp, and not just a date. Although, I though that MySQL could handel that?

My SQL offers a variety of date/time formats for it's dbs (Date, DateTime, Timestamp, Time, & Year). Which one did you choose for the column "downloaded"? Whatever you chose, the var CURRENT_DATE must match the db format in order for the Query Lisa gave you to work.

[Soupisgoodfood]

I used DATETIME (YYY-MM-DD HH:MM:SS). CURRENT_DATE returns YYYY-MM-DD. I can get it to work with days months and years indervidualy E.G. MONTH(downloaded) = CURRENT_MONTH, but there is no way I compare the whole date, without the time.

[Lisa]

Alright use this query then...

SELECT
*
FROM
downloads
WHERE
downloaded like DATE_FORMAT(NOW(), '%Y-%m-%d %%');

It should work for you.

[Soupisgoodfood]

thanks. That works. I've done them all like: ...WHERE DATE_FORMAT(downloaded, '%Y %m') = DATE_FORMAT(NOW(), '%Y %m'); For the previous ones. I use the same except put a SUBDATE in the date part of the DATE_FORMAT. they all work fine except I'm stuck on trying to figure out how to calculate last week since the is no WEEK interval for SUBDATE/ADDDATE, and I'm not sure if it's possable to put calculations in the DATE_FORMAT, like (%u -1) etc.

[Lisa]

If you have a scripting langauge like PHP it should be easy to figure out what day is Sunday and then calculate the last 7 days on that.