Mysql Column send_date datetime format given below
1423305800
1423305866
1423305932
1423305998
1423306064
How to select only date from this table, my coding is:
$date_from_calendar = strtotime('2015-02-06');
$sql = "select * from use_info where user_id='$user_id' and date(send_date)= '$date_from_calendar' order by u_id DESC";
Why i am unable to get results from it?
How about this, format it before comparing
$date_from_calendar = strtotime('2015-02-06');
$sql = "select * from use_info where user_id='$user_id' and unix_timestamp(from_unixtime(send_date, '%Y-%m-%d')) = '$date_from_calendar' order by u_id DESC";
In php strtotime() function will convert a string date like ('2015-02-06') to timestamp which is what you used in your database, so you don't need to use date() function in your query, here is corrected code:
$thatday = '2015-02-06';
$sql = "select * from use_info where user_id='$user_id' and date(send_date)= '$thatday' order by u_id DESC";
If the send_date field is of type timestamp then you can convert it to a date.
$sql = "select * from use_info
where user_id='$user_id'
and date(send_date) = '2015-02-06'
order by u_id DESC";
In the question you converted the timestamp to a date and compared it to a timestamp.
Alternatively you can "manually" search the entire day.
date_default_timezone_set('UTC');
$start = strtotime('2015-02-06'); // start of the day 1423180800
$end = $start + (60 * 60 * 24); // 24 hours after $start 1423267200
$sql = "select * from use_info
where user_id = $user_id
and send_date >= $start
and send_date < $end
order by u_id DESC";
In the example above, we are matching all the records that occurred on or after 1423180800 and before 1423267200.
2015-02-06 to 2015-02-07 correct? Or do you only want records that occurred for the second of 1423305800?1423305800 only represents one second. It is recording the number of seconds that have elapsed since Jan 01 1970 (sometimes called the unix epoch). So when you want to get all the records that happen with in a 24 hour period you need to account for all the seconds that have elapsed in that period. You can convert from timestamp to date in mysql but there are a few hitches, for one the field to be converted needs to be a mysql datetime expression or a TIMESTAMP field. stackoverflow.com/questions/9251561/….