Select Date Range From Database

I'm a beginner, so treat me like an idiot...

Assume one of the fields in my database is a timestamp field with whatever default format mysql creates.

Now if I want to do a select query from php where I want to select all records from today... or the past 7 days, how do I form the WHERE part of the query?

The pseudo code would go like this: "SELECT * from mytable WHERE date = today".

I tried to replace "today" with CURRENT_DATE and it doesn't work. What am I missing?

 

SELECT * from mytable WHERE date = now()

SELECT * from mytable WHERE date > DATE_SUB(DATE(NOW()),INTERVAL 7 DAY)

 

This didn't seem to work. Is it because it is trying to match the current time as well as the current date to the timestamp field in the database?

How do I convert this and my timestamp field so that they are only trying to match dates and not times?

Thanks

 

Ok, looking closer, the dates in my database are stored as DATETIME format. The problem is getting php to generate a date in the same format in order to query the database.

So, how do I do a "select" from table where DATETIME field = today ?

 

SELECT
	myColumn1,
	myColumn2
FROM
	myTable
WHERE
	myDateTimeColumn
REGEXP
	CONCAT('^', CURDATE())

Code (markup):

 

Convert NOW() & dateintable using to_days() and compare

SELECT * from mytable WHERE to_days(now()) = to_days(dateintable)

 

If if you want php to do the work.... this works as well...admittedly this is probably hackish...but it works

This way you get SQL and PHP using the same times.

//returns all entries done just today, from 00:00:00am to 11:59:59 PM 

$start_time = mktime(0,0,0, date("n"), date("j"), date("Y") );
$end_time = strtotime("+1 day", $start_time) - 1; //this takes you to the last second of the current day.... 

$sql = "SELECT * FROM `database` WHERE `timestamp` >= $start_time AND `timestamp` <= $end_time";

Code (markup):

 

Hello,

You can use

where timestamp BETWEEN startime and endtime more info here

http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html#function_between


Best regards,
Jakomo

 

Don't forget that date is a reserved word in SQL so if you have a table called date, you need backticks when you reference it:

SELECT * FROM mytable WHERE `date` = NOW()

Code (markup):

As you said, it's a timestamp / datetime so if you want just the date, you use the DATE() function:

SELECT * FROM mytable WHERE DATE(`date`) = NOW()

Code (markup):