Select * from table where date = today

Question

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 the table where the unixtime stamp = today.

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

I'd go for the SQL version:

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

Answer 5

SELECT * FROM tbl WHERE date >= CURDATE()

Answer 6

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