Magento 2 Collection Date Filter out by one hour

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 MagentoFrameworkStdlibDateTimeDateTimeFactory;
use MagentoCustomerModelCustomer;   

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 MagentoFrameworkStdlibDateTimeTimezoneInterface;
use MagentoCustomerModelCustomer;

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 MagentoFrameworkStdlibDateTimeDateTimeFactory;
use MagentoFrameworkStdlibDateTimeTimezoneInterface;
use MagentoCustomerModelCustomer;   

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 MagentoFrameworkStdlibDateTimeDateTimeFactory;    

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