Select * from table where date = today

Need help with PHP/MySql. Need to select all the records from 'today'.

My table has a coloumn which holds a unixtime stamp and I want to just select from t开发者_JAVA百科he table where the unixtime stamp = today.

Happy to do this on linux command line just need to basic MySql query?

I'd go for the SQL version:

SELECT * FROM table WHERE DATE(timestamp_field) = CURDATE();

Since you tagged PHP I'll provide a PHP answer:

list ( $y, $m, $d ) = explode('.', date('Y.m.d')); 
$today_start = mktime(0, 0, 0, $m, $d, $y);
$today_end   = mktime(23, 59, 59, $m, $d, $y);
// do the query with this clause:
// ... WHERE unix_timestamp BETWEEN $today_start AND $today_end

Here are the proper ways of doing that:

WHERE `date` BETWEEN UNIX_TIMESTAMP(DATE(NOW())) AND UNIX_TIMESTAMP(DATE(DATE_ADD(NOW(), 1 DAY)))

if there is no values in the future (tomorrow, 2 days after tomorrow, etc) then it can be simplified to:

WHERE `date` >= UNIX_TIMESTAMP(DATE(NOW()))

and when you change your date field type to datetime the possible query will be:

WHERE `date` >= DATE(NOW())

When I say "proper" I mean that these queries can be optimized by using indexes. And queries like DATE(order_time) = DATE(NOW()) - cannot, because it is an expression over the field.

SELECT * FROM table_name WHERE DATE(date) = DATE(NOW())

A suitable WHERE clause might be:

CAST( FROM_UNIXTIME( <your_field> ) AS DATE ) = CURDATE( )

• FROM_UNIXTIME( ) - converts to MySQL DATETIME
• CAST( AS DATE ) - gets just the date part
• CURDATE( ) - gets the current date

SELECT * FROM tbl WHERE date >= CURDATE()

$q="select * from table where date >'".date("Y-m-d 00:00:00")."' and date <'".date("Y-m-d 23:59:59")."'";

on my table that has a datetime column, I use this successfully

 SELECT * FROM orders WHERE DATE(order_time) = DATE(NOW())