blog, Couchbase

Couchbase Date Functions Part 2

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
FROM sales
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
FROM sales
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’)

It will be interesting to see the next update and see if I can run the streamlined query using the Couchbase date/time functions.

Leave a Reply