CODEWITHSUNDEEP
phpmysql
Durga
Durga

Reputation: 1303

compare today's date with unix timestamp value in database

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: 1661

Answers (2)

Gerald Schneider
Gerald Schneider

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

Siim Kallari
Siim Kallari

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

Related Questions