Incorrect DATE values

[ms061210]

I have this code, where I want to get inclusive dates between two dates. Date1 is the date to day and Date2 is the date after 7 days. I am having problem because the code returns date from the past day, How should I fix it? This is my query.

$date = date('Y-m-d'); //current date
$date1 = strtotime("+7 day"); //7 days from now
$date2 = date('Y-m-d', $date1);

$result = mysqli_query($connection,"SELECT os_no, total, balance, discount, date_order, date_pickup, time_pickup
                        FROM order_customer
                        WHERE Date(date_pickup) between '$date' AND '$date2'
                                          ");

The code returns this:

My date_pick up is of TIMESTAMP type. Thanks in advance.

[ryantroop]

Just a thought... where are you located vs where your server is located?

If you are in (just a shot in the dark here) Asia, but the server is in the USA, the date function will use the server's local time, not your time. Is this perhaps the problem?

[ms061210]

I tried adding date_default_timezone_set('Asia/Manila'); but still the same output :(

[diafol]

Use the datetime object

$dt = new DateTime(null, new DateTimeZone('Asia/Manila'));
$date1 = $dt->format("U");
$dt->add(new DateInterval('P7D'));
$date2 = $dt->format("U");

$result = mysqli_query($connection, "SELECT os_no, total, balance, discount, date_order, date_pickup, time_pickup FROM order_customer WHERE date_pickup BETWEEN '$date1' AND '$date2'");

However, as your are using mysqli, I don't see why you're not using prepared statements.

[urtrivedi]

You can also try directly using pure mysql query without php

$result = mysqli_query($connection, "SELECT os_no, total, balance, discount, date_order, date_pickup, time_pickup FROM order_customer 
WHERE DATE( date_pickup  ) 
BETWEEN DATE_SUB( current_date, INTERVAL 7 DAY ) AND current_date");