Reputation: 1303
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
query
$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
Upvotes: 1
Views: 1660
Reputation: 17797
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:
WHERE DATE(FROM_UNIXTIME(jsp_date))=CURDATE()
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.
Upvotes: 4
Reputation: 861
You could use PHP DateTime class.
$today = new DateTime('now'); // Can be what ever you want, not only now
// see below for reference.
$jobpostdate = $today->getTimestamp();
and query:
$sql = "SELECT jsp_title, jsp_subtitle, jsp_desc, jsp_location, jsp_date ";
$sql .= "FROM js_projects WHERE jsp_date = '$jobpostdate' ";
!PS I can't test it, but in a nutshell it should work.
Fore more reference about this awesome class go here: http://php.net/manual/en/class.datetime.php
Upvotes: -1