As mentioned in my previous post we have a json document with a sold dealDate in the stored in the format YYYY-MM-DD. I was testing some queries to get sales by month, week or day. The query worked and I was able to optimize the indexing to make the query stupid fast even with tens of thousands of test records.
Then I noticed a problem.
I was comparing the monthly sold totals and there were a few months where the count was off. I then ran a query for one salesperson who had sales each month. Looking at 1 years worth of data there were two months where the count was off by 1 sale.
In the WHERE clause of the query we were using the following statement to filter by month:
DATE_PART_STR(dealDate, ‘month’) = DATE_TRUNC_STR(NOW_LOCAL(‘1111-11-11’), ‘month’)
Running the following query showed that the month for any deal with the date being the first day of the month converted to the last day of the previous month:
SELECT dealDate as deal_a_month
, DATE_TRUNC_STR(dealDate, ‘month’) as deal_b_month
, DATE_TRUNC_STR(STR_TO_TZ(dealDate, ‘America/New_York’), ‘day’) as deal_c_month
, DATE_TRUNC_STR(NOW_LOCAL(‘1111-11-11’), ‘month’) as now_month
WHERE dealDate LIKE ‘2018-%-01’ (when changed to ‘2017-%-01’ we confirmed that every first day of the month was showing the previous month)
Running several versions of this query and using several date changes the issue was confirmed. I wondered if I was doing something wrong. So I created a post on the Coucbase form.
Within 2 hours someone from Coucbase responded that I had located a bug that they were already aware of and working on.
I played around with the query some and came up with a solution that works:
SELECT customerName, customerKey, dealDate
WHERE DATE_PART_STR(dealDate, ‘year’) = DATE_PART_STR(DATE_ADD_STR(NOW_LOCAL(), 0, ‘month’), ‘year’)
AND DATE_PART_STR(dealDate, ‘month’) = DATE_PART_STR(DATE_ADD_STR(NOW_LOCAL(), 0, ‘month’), ‘month’)
ORDER BY dealDate DESC
It will be interesting to see the next update and see if I can run the streamlined query using the Couchbase date/time functions.