DATE_TRUNC_STR Puts First Day of Month Back One Month?

N1QL
1

We have a sold dealDate in the json document stored in the format YYYY-MM-DD.

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

or ultimately (for previous month):
DATE_PART_STR(dealDate, ‘month’) = DATE_ADD_STR(DATE_TRUNC_STR(NOW_LOCAL(‘1111-11-11’), ‘month’), -1, ‘month’)

We noticed that there was one sale missing.

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)

The output from the query is:
{
“deal_a_month”: “2018-02-01”,
“deal_b_month”: “2018-01-01”,
“deal_c_month”: “2018-01-31”,
“now_month”: “2018-02-01”
}

This was working towards a solution to be able to get a total of last months deals. The test query:
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_ADD_STR(DATE_TRUNC_STR(dealDate, ‘month’), -1, ‘month’) as deal_last_month
, DATE_TRUNC_STR(NOW_LOCAL(‘1111-11-11’), ‘month’) as now_month
, DATE_ADD_STR(DATE_TRUNC_STR(NOW_LOCAL(‘1111-11-11’), ‘month’), -1, ‘month’) as last_month
FROM sales
WHERE dealDate LIKE ‘2018-%-01’

Resulted in:
{
“deal_a_month”: “2018-02-01”,
“deal_b_month”: “2018-01-01”,
“deal_c_month”: “2018-01-31”,
“deal_last_month”: “2017-12-01”,
“last_month”: “2018-01-01”,
“now_month”: “2018-02-01”
}

Why is the first day of the month backing up a month? Does the dealDate need to be a timestamp instead of simply a date string?

NOTE: I had a thought that the date/time on the server might be off. So I checked the server and the date is showing as: Tue Feb 20 10:29:08 EST 2018

2

The issue is fixed in next releases MB-27862

Alternative will be SUBSTR(“2017-03-15 01:25:36+0000”,0,8) || “01”

3

Thanks for the quick reply.

I did find another query that did solve the issue and allows it to work (with a few extra lines of code):

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’)
ORDER BY dealDate DESC