CODEWITHSUNDEEP
phpmysqlsqltimestamp
Jerielle
Jerielle

Reputation: 7520

How to create a date range query using Mysql timestamp?

just need a little help here I hope you can help me with my simple problem.

I have a table that consist of shops data. In my PHP page the user can register their shops. And in registration the day that the user registered his/her account will be save in the database table and will format as a TIMESTAMP.

I have no problem with that. In my backend page there is a form for advance searching method. In my form, there is a dropdown field and beside it there is a datepicker that will format as mm/dd/yyyy so I have a 2 datepicker for that which is from and to.

My problem is:

I formatted the datepicker result using the strtotime() function in PHP. And I have a query like this.

$query = "SELECT * FROM shops
          WHERE status = '".$status."'
          AND registered BETWEEN ('".$from." AND ".$to."')";

Sample output of query is:

 SELECT * FROM shops WHERE status = 'P' AND registered BETWEEN ('1388505600' AND '1391097600');

Now I have a timestamp result for $from and $to. How can I filter it in mysql?

Ok that's all. Thanks in advance.

Upvotes: 1

Views: 1885

Answers (4)

Mike Brant
Mike Brant

Reputation: 71422

You should actually format a proper YYYY-MM-DD string format in PHP.

There basically is rarely ever a reason to work with UNIX timestamps in MySQL.

So do something like this for each of your datepicker to/from values:

$to_datetime = DateTime::createFromFormat('m/d/Y|', $datepicker_to);
$to = $to_datetime->format('Y-m-d');
$from_datetime = DateTime::createFromFormat('m/d/Y|', $datepicker_from);
$from = $from_datetime->format('Y-m-d');

And then use them in your query:

$query = "SELECT * FROM shops
          WHERE status = '".$status."'
          AND registered BETWEEN ('".$from."' AND '".$to."')";

Note that I have used PHP DateTime class, which I would HIGHLY recommend as your go to tool for date manipulation in PHP (along with it's related classes like DateInterval, DatePeriod, DateTimeZone, etc.)

Upvotes: 3

Mehdi
Mehdi

Reputation: 4318

PHP

// $t is the value you got from strtotime()
$FormattedDateTime = date('Y-m-d H:i:s', $t)

Upvotes: 1

Fabien Warniez
Fabien Warniez

Reputation: 2741

You need to remove the quotes around the timestamps and you need to use FROM_UNIXTIME() if the column registered is not in UNIX time already.

Upvotes: 1

Tin Tran
Tin Tran

Reputation: 6202

I think you need to use FROM_UNIXTIME

$query = "SELECT * FROM shops WHERE status = '".$status."' AND registered BETWEEN FROM_UNIXTIME(".$from.") AND FROM_UNIXTIME(".$to.")";

Upvotes: 1

Related Questions