Date filtering returning wrong results - timezone issue? I'm running into an issue where incorrect results are being returned when filtering Craft Commerce orders by date in a template. Can anyone help? I cannot figure out what is going on.

FACTS:


Craft install is using America/Denver for timezone.
I'm passing dates via the query string with this format: YYYY-mm-dd
PHP version is 7.1.
Problem timestamp from the DB is (2017-11-10 00:14:04)... this doesn't match the screenshot below because I've been fiddling with dates.




This is my reduced code:

{% set start = "2017-11-09" | date('Y-m-d 00:00:00', 'America/Denver')  %}
{% set end = "2017-11-09" | date('Y-m-d 23:59:59', 'America/Denver') %}
{% set orders = craft.commerce.orders.dateOrdered('and', '&gt;= ' ~ start, '&lt;= ' ~ end).find() %}

{% for order in orders %}
    {{ order.dateOrdered | date('Y-m-d H:i:s e') }}&lt;br/&gt;
{% endfor %}




This is the returned data with the incorrect entry:
Essentially you're running into this same issue: Using user-selectable timezones to show localized post dates

Not terribly pretty, but as a workaround, you can do something like this:

{% set start = date("2017-11-09 00:00:00")|date_modify("+7 hours")|date('Y-m-d h:i:s')  %}
{% set end = date("2017-11-09 23:59:59")|date_modify("+7 hours")|date('Y-m-d h:i:s')  %}

{# +7 hours is because America/Denver is UTC-7, but would need to be adjusted to +6 hours when observing daylight savings time #} 

{% set orders = craft.commerce.orders.dateOrdered('and', '&gt;= ' ~ start, '&lt;= ' ~ end).find() %}

{% for order in orders %}
    {{ order.dateOrdered | date('Y-m-d H:i:s e') }}&lt;br/&gt;
{% endfor %}


Also, note that this should "just work" in the upcoming Craft 3 as well.

There might be a cleaner way to do this using @carlcs's workaround, but I'm not seeing it at the moment.