mysql php - select * where date = today?

[c0rrup]

Hello, I am trying to do a mysql query to get all the rows where date = "today".....

The problem is, I store my downloads date using:
$date = time();

is a mysql text field, which I'm guessing is really bad / slow?

Can anyone provide me with a better way to store dates and then also provide me with a mysql_query to select all that have been added today.


Thank you.

c0rrup Reviewed by c0rrup on 8th Feb 2011. mysql php - select * where date = today? Hello, I am trying to do a mysql query to get all the rows where date = "today"..... The problem is, I store my downloads date using: $date = time(); is a mysql text field, which I'm guessing is really bad / slow? Can anyone provide me with a better way to store dates and then also provide me with a mysql_query to select all that have been added today. Rating: 5

[Lock Down]

Date should be a numeric 11 byte int field for the date storing. Be sure to use a key index of the date.

for Today only
select * from table WHERE substring(from_unixtime(my_date),1,10) = CURDATE()

[Buddah]

Yes, all timestamps should be set as int.

I would avoid using any extra MySQL processing, though, especially since you're generating your original timestamp with PHP's time().

Something like this would suffice:

$today = strtotime('12:00:00');

$sqlQuery = "SELECT * FROM theTable WHERE the_time >= {$today}";

[Lock Down]

A little Mickey Mouse.. And won't work..... Adding extra undeeded code is really bad programming.

[Buddah]

If you create a time stamp within PHP, you should compare it with PHP. Mostly because within the PHP system, an offset may be used within the time function or the date_default_timezone_set. Inconsistent code is really bad programming.

[Lock Down]

You are kidding. Right? Where did you get your Mysql training?

So you want to create a variable in php and read the the mysql data and move the date field to a php on each record then compare?

Well that is exactly what you just said. You just don't quit do you.. Nothing in your head but book learning. No real experience in database design and implementation.

It is your opinion but keep it to yourself.

[Buddah]

You're brilliant.

You'd rather two functions be executed over every row to make a comparison. Instead of one simple comparison, you'd rather over complicate it in an irrational way.

I lack experience? Yeah... that's why you're telling someone to use an expensive string function and an unnecessary timestamp conversion on EVERY ROW.

[immortalxx]

if u dont understand time() or mktime(), just store ur dates using date field. google mysql date

if ur storing using time(), change ur field type to INT, since its faster then using VARCHAR or CHAR or similar.

[Lock Down]

You're brilliant.

Thank You.


You should really spend some time running benchmarks and testing before yipping and yapping..

[Buddah]

You're 100% right. It's perfectly logical that a query with two functions is faster than one with 0 functions.

Again, sheer brilliance.