<p>Essentially you're running into this same issue: <a href="https://craftcms.stackexchange.com/questions/18102/using-user-selectable-timezones-to-show-localized-post-dates">Using user-selectable timezones to show localized post dates</a></p>

<p>Not terribly pretty, but as a workaround, you can do something like this:</p>

<pre><code>{% 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 %}
</code></pre>

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

<p>There might be a cleaner way to do this using <a href="https://craftcms.stackexchange.com/a/18110/57">@carlcs's workaround</a>, but I'm not seeing it at the moment.</p>

CC BY-SA 3.0
2017-11-10T23:54:50.363Z
Many thanks to Brad for tracking down a fix. I'd imagine most people who are filtering results by date are in fact getting incorrect results on the very edges of the date range since they aren't using a hardcoded UTC offset.
Anna_MediaGirl
CC BY-SA 3.0
2017-11-10T23:43:53.367Z
2017-11-10T23:57:18.200Z
2017-11-10T23:57:18.200Z
Brad Bell
<p>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.</p>

<p><strong>FACTS:</strong></p>

<ul>
<li>Craft install is using <code>America/Denver</code> for timezone.</li>
<li>I'm passing dates via the query string with this format: <code>YYYY-mm-dd</code></li>
<li>PHP version is 7.1.</li>
<li>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.</li>
</ul>

<hr>

<p>This is my reduced code:</p>

<pre><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 %}
</code></pre>

<hr>

<p>This is the returned data with the incorrect entry:
<a href="https://i.sstatic.net/wvj3N.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/wvj3N.png" alt="enter image description here"></a></p>

CC BY-SA 3.0
2017-11-10T17:51:04.430Z
FWIW, `date('Y-m-d')` is YYYY-MM-DD (zero-padded double digits for month and day), not YYYY-M-D. Not sure if that would make a difference or not, but it might affect the variables you're setting in the first two lines.
Sandwich
CC BY-SA 3.0
2017-11-10T18:48:45.273Z
@Sandwich so the date being passed via the query string would need to be `2017-11-09`, correct? Or the date format character would be changed from d to j like so: `date('Y-m-j')`, yes?
Natetronn
CC BY-SA 3.0
2017-11-10T19:04:57.080Z
@Natetronn Yes to the first, and I can't recommend ever using single digits for anything time-related for the second. ;)
Sandwich
CC BY-SA 3.0
2017-11-10T19:46:49.403Z
Thank you for this suggested direction... going to did into it right now. I thought I was mitigating it because I was changing to the required format before submitting the query. Will report back.
Anna_MediaGirl
CC BY-SA 3.0
2017-11-10T20:16:26.587Z
Same results after adjusting the format for the date I'm sending to the page. I'm updating my question to reflect the changed date format.
Anna_MediaGirl
CC BY-SA 3.0
2017-11-10T20:55:58.623Z
If you output the raw timestamps for those orders and run them through some online timestamp converter, what does it say? Also, it could be that the PHP version has timezone-related bugs, so that might be worth looking into.
Sandwich
CC BY-SA 3.0
2017-11-10T21:56:22.180Z
https://bugs.php.net/search.php?cmd=display&order_by=id&direction=DESC&limit=30&package_name[]=Date%2Ftime+related
Natetronn
CC BY-SA 3.0
2017-11-10T22:09:49.230Z
PHP version is 7.1 and the problem timestamp from the DB is (2017-11-10 00:14:04)
Anna_MediaGirl
CC BY-SA 3.0
2017-11-10T22:19:33.460Z
I'm looking through the PHP bug list but honestly isn't a little overwhelming. I don't know what I'm looking for since I can't 100% explain what's happening.
Anna_MediaGirl
<datetime><ecommerce><timezone>
Date filtering returning wrong results - timezone issue?
# Date filtering returning wrong results - timezone issue?

(Asked by: username_0 on 2017-11-10)

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') }}
{% endfor %}


This is the returned data with the incorrect entry:

**Tags:** datetime, ecommerce, timezone

> **Comments:**
> **username_1**: FWIW, `date('Y-m-d')` is YYYY-MM-DD (zero-padded double digits for month and day), not YYYY-M-D. Not sure if that would make a difference or not, but it might affect the variables you're setting in the first two lines.  
> **username_2**: @username_1 so the date being passed via the query string would need to be `2017-11-09`, correct? Or the date format character would be changed from d to j like so: `date('Y-m-j')`, yes?  
> **username_1**: @username_2 Yes to the first, and I can't recommend ever using single digits for anything time-related for the second. ;)  
> **username_0**: Thank you for this suggested direction... going to did into it right now. I thought I was mitigating it because I was changing to the required format before submitting the query. Will report back.  
> **username_0**: Same results after adjusting the format for the date I'm sending to the page. I'm updating my question to reflect the changed date format.  
> **username_1**: If you output the raw timestamps for those orders and run them through some online timestamp converter, what does it say? Also, it could be that the PHP version has timezone-related bugs, so that might be worth looking into.  
> **username_2**: https://bugs.php.net/search.php?cmd=display&order_by=id&direction=DESC&limit=30&package_name[]=Date%2Ftime+related  
> **username_0**: PHP version is 7.1 and the problem timestamp from the DB is (2017-11-10 00:14:04)  
> **username_0**: I'm looking through the PHP bug list but honestly isn't a little overwhelming. I don't know what I'm looking for since I can't 100% explain what's happening.  

---

## 1 answers

### Answer by username_3 on 2017-11-10. Score: 1 | Accepted answer

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') }}
{% 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.

> **Comments:**
> **username_0**: Many thanks to Brad for tracking down a fix. I'd imagine most people who are filtering results by date are in fact getting incorrect results on the very edges of the date range since they aren't using a hardcoded UTC offset.  

---

