Magento 2 Collection Date Filter out by one hour

Question

I have an issue where I filter a collection by date and the item I expect to get is not being returned in the collection, however if I print out the SQL that the collection uses and run that against my database the item is returned.

$from = new \DateTime($lsDate);
$orders = $this->_orderCollectionFactory->create()
        ->addFieldToSelect(['grand_total', 'created_at'])
        ->addAttributeToFilter('created_at', array('gteq' => $from->format('Y-m-d H:i:s')))
        ->addAttributeToFilter('customer_id',$customer->getId())
        ->setPageSize(10)
        ->setOrder('created_at', 'desc');

$from->format('Y-m-d H:i:s') // Lets say this is 2019-08-06 15:33:00
$this->logger->info(count($orders)); // This is 0

If I print out the SQL that this generates it looks something like this:

SELECT `main_table`.`entity_id`, `main_table`.`grand_total`, `main_table`.`created_at` FROM `sales_order` AS `main_table` WHERE (`created_at` >= '2019-08-06 15:33:21')

The orders created_at date that should be returned is 2019-08-06 15:34:00.

If i run the above query on my database it returns the one order above however as you can see in my code above the collection is empty.

If i change the date of the order to be 2019-08-06 16:34:21 (one hour in the future) the code then returns a collection with one item. It looks like it has something to do with the timezone somwehere? Maybe DST (Daylight Saving Time)?

EDIT

Here is some more information about the $lsDate variable.

$lsDate comes from a customer attribute. I store the date as so:

 $newDate = new \DateTime();
 $customer->setCustomAttribute('ls_start_date', $newDate->format('Y-m-d H:i:s'));

And get the date as so:

$lsDate = $customer->getCustomAttribute('ls_start_date')->getValue();

Answer 1

To start with, maybe we need a general Magento Date handling explanation.

Magento intend all its dates to be saved in DB in GMT.
The reason for this design choice is quite simple: Magento allows you to configure multi-store that could be in multiples timezones.

Let's imagine I have a Magento with 3 stores, that I do operate from London.
Here are my store:

• My Londonian store, configured in Europe/London; this is also my Main Store configuration
• A store for Japan, configured in Asia/Tokyo timezone
• A store for north America, configured with timezone America/New_York

And now, let's take a business case were I do promise my customer that "We deliver in 48 hour, worldwilde, based on the timezone of the capital city of the country you live in.".

Then I get 3 orders, ones for each stores, all of them on the 1st of May at 16:15.
That would be extremly unconveniant for me, in the admin, to have all the three orders stated as placed on the 1st May at 16:15, to fulfill what I promise to my customer , because I would have to do crazy calculation based on the store I see in the admin grid of the orders.

The best for me would be to see

1. One order placed on 1st May at 16:15, in the Londonian store
2. One order placed on 30rd April at 20:15, in the Tokyo store
3. One order placed on 1st May at 21:15, in the New York store

Magento, in order to do that, would retrieve the date in GMT from the database and then just apply the current sotre timezone to the date.
Quite easy.

Imagine the complexity it would have be if they did store timezoned dates in the database... Magento would need to store both the date AND the timezone and do conversions back and forth or timezones computation for any single date you have to display.
Pretty crazy job to do.

So your best bet, in order to follow Magento's way of working would be to store your dates in the database in GMT, and so to create your customer date this way:

use Magento\Framework\Stdlib\DateTime\DateTimeFactory;
use Magento\Customer\Model\Customer;   

class CustomerLsDate {
    private $dateTimeFactory; 

    public function __construct(DateTimeFactory $dateTimeFactory) {
        $this->dateTimeFactory = $dateTimeFactory;
    }

    public function setLsDate(Customer $customer): CustomerLsDate {
        $customer->setCustomAttribute('ls_start_date', $this->dateTimeFactory->create()->gmtDate('Y-m-d H:i:s'));

        return $this;
    }
}

Then, when you want to query on this date, just use it as is.
If you want to diplsay it to someone, in the store timezone, then:

use Magento\Framework\Stdlib\DateTime\TimezoneInterface;
use Magento\Customer\Model\Customer;

class CustomerLsDate {
    private $timezone;

    public function __construct(TimezoneInterface $timezone) {
        $this->timezone = $timezone;
    }

    public function getLsDate(Customer $customer): string {
        $date = $this->timezone->date(
            new \DateTime(
                $customer->getCustomAttribute('ls_start_date')->getValue(),
                new \DateTimeZone('GMT')
            )
        );

        Zend_Debug::dump($date->format('Y-m-d H:i:s'));

        return $date->format('Y-m-d H:i:s');
    }  
}

That would really be the approach fitting most with Magento philosophy

Full CustomerLsDate class:

use Magento\Framework\Stdlib\DateTime\DateTimeFactory;
use Magento\Framework\Stdlib\DateTime\TimezoneInterface;
use Magento\Customer\Model\Customer;   

class CustomerLsDate {
   private $dateTimeFactory; 
   private $timezone;

   public function __construct(DateTimeFactory $dateTimeFactory, TimezoneInterface $timezone) {
       $this->timezone = $timezone;
       $this->dateTimeFactory = $dateTimeFactory;
   }

   public function setLsDate(Customer $customer): CustomerLsDate {
       $customer->setCustomAttribute(
           'ls_start_date', 
           $this->dateTimeFactory->create()->gmtDate('Y-m-d H:i:s')
       );

       return $this;
   }

   public function getLsDate(Customer $customer): string {
       $date = $this->timezone->date(
           new \DateTime(
               $customer->getCustomAttribute('ls_start_date')->getValue(),
               new \DateTimeZone('GMT')
           )
       );

       Zend_Debug::dump($date->format('Y-m-d H:i:s'));
       return $date->format('Y-m-d H:i:s');
    }  
}

---

Rick James have part of the answer.

Since created_at is a timestamp and that a default connection to MySQL will apply the server timezone to a timestamp, your manual query works.

But now if you go like Magento and do

SET time_zone = '+00:00'; 
SELECT `main_table`.`entity_id`, `main_table`.`grand_total`, `main_table`.`created_at` FROM `sales_order` AS `main_table` WHERE (`created_at` >= '2019-08-06 15:33:21');

Your query won't return any result like your Magento collection does.
The time_zone setting of Magento is done in their default PDO adapter implementation here:

/**
 * Creates a PDO object and connects to the database.
 *
 * @SuppressWarnings(PHPMD.CyclomaticComplexity)
 * @SuppressWarnings(PHPMD.NPathComplexity)
 *
 * @return void
 * @throws \Zend_Db_Adapter_Exception
 * @throws \Zend_Db_Statement_Exception
 */
protected function _connect()
{
    // extra unrelated code comes here...

    // As we use default value CURRENT_TIMESTAMP for TIMESTAMP type columns we need to set GMT timezone
    $this->_connection->query("SET time_zone = '+00:00'");

    // extra unrelated code comes here...
}

Source: Magento/Framework/DB/Adapter/Pdo/Mysql

From there on, your answer lies in where your variable $lsDate is coming from and if you are able to know its timezone, in order to translate it back to GMT, to have the correct GMT date to give to your collection filter.

For example, if you know that your timezone is 'Europe/London' you can do

$date = new \DateTime('2019-08-06 15:33:21', new \DateTimeZone('Europe/London'));
$date->setTimezone(new \DateTimeZone('GMT'));
echo $date->format('Y-m-d H:i:s'); // echoes 2019-08-06 14:33:21

And from your edit, when you do create a new \DateTime() you will get the a DateTime bound to the timezone of your server.

---

So based on your liking you can either save the date in your custom customer field in GMT, or save the timezone as well as the date.

1. Saving the date in GMT in the customer

Either the PHP way

$newDate = new \DateTime('now',new \DateTimeZone('GMT'));
$customer->setCustomAttribute('ls_start_date', $newDate->format('Y-m-d H:i:s'));

And you'll end up having a GMT date on you customer ls_start_date

Or you can also do it more Magento way, with DI:

use Magento\Framework\Stdlib\DateTime\DateTimeFactory;    

class Whatever {
   private $dateTimeFactory; 

   public function __construct(DateTimeFactory $dateTimeFactory) {
       $this->dateTimeFactory = $dateTimeFactory;
   }

   public function assignThatLsDate($customer) {
       $customer->setCustomAttribute('ls_start_date', $this->dateTimeFactory->create()->gmtDate('Y-m-d H:i:s'));
   }
}

2. Saving the date in local timezone in the customer

$newDate = new \DateTime();
$customer->setCustomAttribute('ls_start_date', $newDate->format('Y-m-d H:i:s'));
$customer->setCustomAttribute('ls_start_date_timezone', $newDate->getTimezone ());

Then

$from = new \DateTime(
    $customer->getCustomAttribute('ls_start_date')->getValue(),
    $customer->getCustomAttribute('ls_start_date_timezone')->getValue()
)->setTimezone(new \DateTimeZone('GMT'));

// query to your collection is unchanged

Answer 2

(too complex for a Comment; may lead to an answer.)

In MySQL, do

SHOW VARIABLES LIKE "%zone%";  -- looking for how the timezone is set

SHOW CREATE TABLE ...  -- looking for datatypes used

In particular, is created_at a DATETIME or a TIMESTAMP?