comparing the today's date with timestamp value

roshanjameer

hi,

i have a mysql table which has 3 fields
1.id 2.username 3.time
values
1 abcd 1181027044

i want to fetch only those user id's and usernames whos time is equal to current date.any of your idea will be surely appreciated.

thanks
mrjameer

rincewind456

This should work.

SELECT id, username FROM TABLE_NAME WHERE time =  unix_timestamp(curdate())

nemonoman

What is the datatype of your time field?
DATETIME? TIMESTAMP? DATE? TIME?

If you don't know, use the query:
DESCRIBE

Like DESCRIBE mytableNameHere

roshanjameer

hi,

thanks for your reply.
the data type is varchar(255).i have tested the code but nothing is displaying

thanks
mrjameer

rincewind456

Why on earth is your time being stored as a varchar?

You can't do meaningful or easy comparisons using integers stored as a string.

bradgrafelman

As rincewind456 suggests, if you're storing timestamps... use the DATETIME (or even TIMESTAMP) column type. It just makes more sense, plus it makes your query read more logically:

SELECT id, username FROM TABLE_NAME WHERE DAY(time) = DAY( CURDATE() )