Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-10T23:54:50.363Z
Answers.Text: 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.
Answers.UserDisplayName: Anna_MediaGirl
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-10T23:43:53.367Z
Answers.LastActivityDate: 2017-11-10T23:57:18.200Z
Answers.LastEditDate: 2017-11-10T23:57:18.200Z
Answers.UserDisplayName: Brad Bell
Body: <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>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T17:51:04.430Z
Comments.Text: 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.
Comments.UserDisplayName: Sandwich
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T18:48:45.273Z
Comments.Text: @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?
Comments.UserDisplayName: Natetronn
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T19:04:57.080Z
Comments.Text: @Natetronn Yes to the first, and I can't recommend ever using single digits for anything time-related for the second. ;)
Comments.UserDisplayName: Sandwich
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T19:46:49.403Z
Comments.Text: 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.
Comments.UserDisplayName: Anna_MediaGirl
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T20:16:26.587Z
Comments.Text: 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.
Comments.UserDisplayName: Anna_MediaGirl
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T20:55:58.623Z
Comments.Text: 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.
Comments.UserDisplayName: Sandwich
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T21:56:22.180Z
Comments.Text: https://bugs.php.net/search.php?cmd=display&order_by=id&direction=DESC&limit=30&package_name[]=Date%2Ftime+related
Comments.UserDisplayName: Natetronn
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T22:09:49.230Z
Comments.Text: PHP version is 7.1 and the problem timestamp from the DB is (2017-11-10 00:14:04)
Comments.UserDisplayName: Anna_MediaGirl
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-10T22:19:33.460Z
Comments.Text: 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.
Comments.UserDisplayName: Anna_MediaGirl
Tags: <datetime><ecommerce><timezone>
Title: Date filtering returning wrong results - timezone issue?
ThreadText: # 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') }}<br/>
  {% endfor %}
  
  ```
  
  ---
  
  This is the returned data with the incorrect entry:
  [![enter image description here](https://i.sstatic.net/wvj3N.png)](https://i.sstatic.net/wvj3N.png)
  
  **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](https://craftcms.stackexchange.com/questions/18102/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](https://craftcms.stackexchange.com/a/18110/57), but I'm not seeing it at the moment.
  
  > **Comments:**
  > **username_0**: Many thanks to username_3 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.  
  
  ---
  
  