1

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

  • 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. – Sandwich Nov 10 '17 at 17:51
  • @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 Nov 10 '17 at 18:48
  • @Natetronn Yes to the first, and I can't recommend ever using single digits for anything time-related for the second. ;) – Sandwich Nov 10 '17 at 19:04
  • 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 Nov 10 '17 at 19:46
  • 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 Nov 10 '17 at 20:16
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.

| improve this answer | |
  • 1
    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 Nov 10 '17 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.