2

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?

Roger007
  • 29
  • 5
  • Gordon please read my question carefully than answer , you are giving me wrong suggestion. If iam able to understand it from dev.mysql.com than i don't ask this question on stackoverflow. – Roger007 Feb 08 '15 at 14:52
  • . . You convert the unix time to a date, then you extract the date component. You seem to understand date functions, so the only missing piece is the conversion from unix time to a date. – Gordon Linoff Feb 08 '15 at 14:56
  • please make the correction where i am wrong? – Roger007 Feb 08 '15 at 14:59
  • gordon thats what iam asking $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"; – Roger007 Feb 08 '15 at 15:54

3 Answers3

1

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";
code-jaff
  • 9,230
  • 4
  • 35
  • 56
0

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";
Jafar Akhondali
  • 1,552
  • 1
  • 11
  • 25
  • This will only match records for the first second of the day, not the entire day. – robbmj Feb 08 '15 at 15:05
  • send_date column contains date and time not only date – Roger007 Feb 08 '15 at 15:05
  • but send_date is in this format 1423305800 1423305866 1423305932 1423305998 – Roger007 Feb 08 '15 at 15:12
  • i had try this but no result - $sql = "select * from use_info where user_id='$user_id' and date(send_date)= '2015-02-06' order by u_id DESC"; – Roger007 Feb 08 '15 at 15:21
  • but when i try this iam getting correct result - $sql = "select * from use_info where user_id='$user_id' and send_date= '1423305800' order by u_id DESC"; – Roger007 Feb 08 '15 at 15:22
  • 1423305800 is date and time both , iam getting 1 result only of this date and time , i want result for whole day – Roger007 Feb 08 '15 at 15:23
0

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.

robbmj
  • 16,085
  • 8
  • 38
  • 63
  • Iam getting correct answer if i do this $sql = "select * from use_info where user_id='$user_id' and send_date= '1423305800' order by u_id DESC"; 1423305800 is date and time , i only want to do for date – Roger007 Feb 08 '15 at 15:07
  • You want all the records that occurred in the 24 hour period of `2015-02-06` to `2015-02-07` correct? Or do you only want records that occurred for the second of `1423305800`? – robbmj Feb 08 '15 at 15:14
  • The examples should work then. Are you taking into consideration time zones? – robbmj Feb 08 '15 at 15:18
  • yes i think this will work , but why we cant extract only date from - 1423305800(this is date and time) – Roger007 Feb 08 '15 at 15:27
  • A time stamp such as `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. http://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query. – robbmj Feb 08 '15 at 15:36
  • code-jaff had done it - $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"; – Roger007 Feb 08 '15 at 15:51