In database I am storing date value unix timestamp value for e.g.'1434952110' using
time() now I am trying to compare todays value with this value as below
$jobpostdate = date("Y-m-d");//today's date passing in database to compare
$sql = "SELECT jsp_title, jsp_subtitle, jsp_desc, jsp_location, jsp_date ";
$sql .= "FROM js_projects WHERE DATE(jsp_date) = '$jobpostdate' ";
I tried above query , but even if the value is present I am getting no rows found, where i am going wrong how can I compare date values ? I know how to compare in php but i don't know exactly how to check in query ,Pls any one can help
Best How To :
If I understand correctly you have a unix timestamp in a varchar field and you can't change this.
If you compare the unix timestamp directly you will only get results that match the exact second of the timestamp.
You can use
FROM_UNIXTIME() to convert the timestamp in a date value you can actually use:
This compares the date portion of the timestamp with the current date, giving you all database entries of the given day.
Note: This could create performance problems, since a lot of conversions occur on every request. You should really convert the unix timestamp in the database into a DateTime or Date field.