Operator BETWEEN and dates in Oracle NoSQL Database, how to do?

Question

I want to do a query in nosql database using the BETWEEN operation ? I want to do something like this:

SELECT * FROM table 
WHERE column_timestamp BETWEEN '2021-01-01'
AND '2021-12-31'

How to do this query in NoSQL Database? Is BETWEEN supported ?

Answer 1

In your case because your column seems to be a timestamp, you need to cast the timestamp values to TIMESTAMP type. Then you'd just use <= and >= operators.

Be careful, when doing queries with only dates w/o providing the time. Here a testcase using <, <=, >=, >.

I've inserted 3 rows :

    2021-12-01T00:00:00Z
    2021-12-31T00:00:00Z
    2021-12-31T23:59:59Z

SELECT * FROM TEST
where date > CAST("2021-12-01" as timestamp) and date < CAST("2021-12-31" as timestamp)

no rows selected

SELECT * FROM TEST
where date >= CAST("2021-12-01" as timestamp) and  date <= CAST("2021-12-31" as timestamp)

2 rows : 2021-12-01T00:00:00Z and 2021-12-31T00:00:00Z

SELECT * FROM TEST
where date >= CAST("2021-12-01T00:00:00" as timestamp)and  date <= CAST("2021-12-31T23:59:59" as timestamp)

3 rows : 2021-12-01T00:00:00Z , 2021-12-31T00:00:00Z and 2021-12-31T23:59:59Z

SELECT * FROM TEST
where date >= CAST("2021-12-01" as timestamp) and  date < CAST("2022-01-01" as timestamp) 

3 rows : 2021-12-01T00:00:00Z , 2021-12-31T00:00:00Z and 2021-12-31T23:59:59Z