Converting mysql unix time and using BETWEEN

Question

I have a form where the user can choose 2 dates, hit submit, and the form returns tickets that were created between those 2 dates.

The dates from the form are in this format: mm/dd/year

The dates in mysql are being saved as unix timestamps.

After the submit, I'm converting the date format like this:

  $from = $_POST['from']
  $to = $_POST['to'];
  $from = date("Y/m/d",strtotime($_GET['from']));
  $to = date("Y/m/d",strtotime($_GET['to']));

So that $from is something like year/mm/dd

My query is:

$get = mysql_query("SELECT id FROM tickets WHERE date_created BETWEEN UNIX_TIMESTAMP('$from') AND UNIX_TIMESTAMP('$to')");

This works fine, my problem is, when the from and to dates are equal, meaning the user wants to see tickets for one day only. Is it still possbile to use the same query somehow? Or how do I modify the query?

What I'd do is explicitly set the time on $from and $to to be 00:00:00 and 23:59:59 respectively, can't recall the PHP for it OTOMH — Psytronic
– Psytronic, Commented Apr 1, 2013 at 20:35

Kermit · Accepted Answer · 2013-04-01 20:37:07Z

2

Why not do...

$from = date("Y-m-d 00:00:00",strtotime($_GET['from']));
$to = date("Y-m-d 23:59:59",strtotime($_GET['to']));

Also, be careful as you're mixing $_GET and $_POST. MySQL has built in date/time functions that can handle this type of conversion. It would be better if you used binded parameters.

answered Apr 1, 2013 at 20:37

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kpaulsen · Accepted Answer · 2013-04-01 20:43:05Z

0

Change your $to and $from to use the first second of the day and the last second of the day:

$from = date("Y-m-d 0:0:0", strtotime($_GET['from']));
$to = date("Y-m-d 23:59:59", strtotitme($_GET['to']));

answered Apr 1, 2013 at 20:43

kpaulsen

1

Collectives™ on Stack Overflow

Converting mysql unix time and using BETWEEN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related