Date filtering returning wrong results - timezone issue?

Question

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', '>= ' ~ start, '<= ' ~ end).find() %}

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

---

This is the returned data with the incorrect entry:

Answer 1

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', '>= ' ~ start, '<= ' ~ end).find() %}

{% for order in orders %}
    {{ order.dateOrdered | date('Y-m-d H:i:s e') }}<br/>
{% 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.