how can I select data from MySQL based on date (unix time record)

Question

I have a record of data with unix time date in it

i want to select the row based on the date/month/year only (not with time)

currently Im using something like this

select * 
  from tablename 
 where date > '$today' 
   and date < '$tomorow' 
 LIMIT 1;

how ever this is not that accurate if the $today and $tomorrow have different time (but same date)

is there any better way to do this?

Amber · Accepted Answer · 2010-05-17 23:58:38Z

2

... WHERE DATE(FROM_UNIXTIME(date)) > DATE(FROM_UNIXTIME('$today')) ...

answered May 17, 2010 at 23:58

Amber

531k89 gold badges643 silver badges558 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This would cause the query to use a table scan, which may not be desirable if the table is very large.

Le Hai Chau · Accepted Answer · 2012-10-29 18:45:36Z

0

Use this statement :

 SELECT * FROM TABLE WHERE DATE LIKE 'YEAR-MOTH-DATE%'.

For example I use this for my case:

 SELECT * FROM USER_MANAGER u WHERE DATE_REGISTER LIKE '2012-09-08%';

answered Oct 29, 2012 at 18:45

Le Hai Chau

79110 silver badges11 bronze badges