Convert integer date into date format in where part of mysql query

[zmnmasood]

Hi, this is my table

As you can see in the above table that date is stored in integer format. All I want is to remove time part from this integer date when I will use this date in mysql query.

$event_date=$_POST["event_date"];  //$event_date contains 2017-09-09

$testing_date="SELECT * from testing_date where date('Y-m-d',pub_date)='$event_date'";
$query1=mysqli_query($con,$testing_date);
$result1=mysqli_fetch_array($query1);

I want to fetch all the records of $event_date. So I matched this date with database date column. But the problem is date in database is in integer format and this database date also includes time part in it. And as you see $event_date doesn’t contain any time part. So what should I have to do in the where part of the query so that I can get the exact result.
Also, this date(‘Y-m-d’,pub_date) is not working properly. I used this to convert integer date into date format but I think it’s not converting that’s why my query is not working properly.

[salathe]

You can use the FROM_UNIXTIME() function to convert the Unix timestamp number into a date and time string, then the DATE() function to get only the date part.

For example:

SELECT FROM_UNIXTIME(1504953296); -- returns "2017-09-09T10:34:56Z"
SELECT DATE("2017-09-09T10:34:56Z"); -- returns "2017-09-09"
SELECT DATE(FROM_UNIXTIME(1504953296)); -- returns "2017-09-09"

Putting that together, your statements could look like:

SELECT * from testing_date WHERE DATE(FROM_UNIXTIME(pub_date)) = '2017-09-09';