unix timestamp in select statement

[defanjos]

I need a mysql select statement to get all records in which a unix timestamp field in the db (mydate) matches today's date.

i.e.
select * FROM tbl123 WHERE mydate = date('Y-m-d')

I know i can get the date in the php code like, gmdate("Y-m-d", $mydate), but how can I do it within the sql statement.

thanks in advance

[defanjos]

Why doesn't something like the following work?

SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = DATE(CURDATE())

[defanjos]

or:
SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = CURDATE()

[defanjos]

This seems to work:

SELECT * FROM tbl123 WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) = DATE(from_unixtime(mydate,'%Y-%m-%d'))

not sure if it chooses just records that match curdate(), or any date going back 24 hours from the time the query is run.

I need records just for that day, for example if curdate() is 2014-01-25, i need all records from 2014-01-25 in the database.

[penders]

SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = DATE(CURDATE()) 
SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = CURDATE()

Your first two examples appear to work OK for me. And likewise, so does the following (I think the format string is unnecessary in this case):

SELECT * FROM tbl123 WHERE DATE(FROM_UNIXTIME(mydate)) = CURDATE()

Your last example (which you say works for you) does not work for me, in fact I can't see how this can work. DATE_SUB(CURDATE(),INTERVAL 1 DAY) returns yesterdays date.

Hhmmm, I wonder... is your DB server running in a different timezone? MySQL date/time functions are locale aware, whereas a unix timestamp does not contain any timezone information and usually (should) refer to UTC time.

[defanjos]

Thanks penders, the problem was the timezone in the server was not the same as the CRM (where I was pulling the data from)

Now, SELECT * FROM tbl123 WHERE DATE(from_unixtime(mydate,'%Y-%m-%d') ) = CURDATE(), works fine.