Inaccurate match on date

SQL++
1

In my query I have this:

WHERE ANY el IN d.data SATISFIES el.timestamp BETWEEN "2015-01-02T10:00:00" AND "2015-06-04T06:02:00" END

The problem is that timestamps outside of the range is included in my result, f.eks “2015-06-04T21:03:12” is included. The date is always correct but the hours, minutes and seconds are off. Wrapping el.timestamp and the strings in STR_TO_MILLIS() doesn’t help.

2

Hi @isha, please look into this and file a ticket if needed.

3

What is couchbase version ? Also can you post the data array values where timestamp contains “2015-06-04T21:03:12”

4

Version: 4.5.0-2601 Community Edition (build-2601). Runs on a Linux server.

Query:

SELECT d.panelid, d.sensorid, ARRAY_FLATTEN(ARRAY_AGG(d.data),2) AS data 
FROM `data` d 
WHERE panelid="a-si" and sensorid="voc" AND ANY el IN d.data SATISFIES el.timestamp BETWEEN "2015-01-02T10:00:00" AND "2015-06-04T06:02:00" END 
GROUP BY d.panelid, d.sensorid

files.zip (743.0 KB)

I have put two files in the zip, file ‘result.txt’ is my result from the query, as you can see I get a few timestamps before ‘2015-01-02T10:00:00’ and a lot after ‘2015-06-04T06:02:00’. The ‘data_doc.txt’ file is a data document from the last day, the documents from the days prior is equally structured.

5

@mortii,

ANY el IN d.data SATISFIES el.timestamp BETWEEN “2015-01-02T10:00:00” AND “2015-06-04T06:02:00” END In WHERE clause only decides qualify the document or not it will not change the document. As one of the array element qualifies the document it uses whole document.

Are you looking some thing like this.

SELECT d.panelid, d.sensorid, ARRAY_FLATTEN(ARRAY_AGG(qdata),2) AS data
FROM `data` d
LET qdata = ARRAY el  FOR el IN d.data WHEN el.timestamp BETWEEN "2015-01-02T10:00:00" AND "2015-06-04T06:02:00" END
WHERE panelid="a-si" and sensorid="voc" AND ARRAY_LENGTH(qdata) > 0
GROUP BY d.panelid, d.sensorid;
6

That did the trick, thank you :smiley: