How do I query a tstzrange with Sequelize?

Question

I have a range of timestamp with timezone stored in one column of my PostgreSQL database, e.g:

timeRange (tstzrange)
["2019-02-10 23:00:00+00","2019-03-09 23:00:00+00")

I want to query my database based on that column, testing if a given date range is contained by the range in my column.

According to the PostgreSQL docs, I can query a range contained by another range with the <@ operator:

 Operator | Description           | Example                          | Result
 <@       | range is contained by | int4range(2,4) <@ int4range(1,7) | t

According to the Sequelize docs, this can be done using the operator $contained:

$contained: [1, 2]     // <@ [1, 2) (PG range is contained by operator)

I have tried querying using this operator:

const start = '2019-02-11T00:30:00.000Z';
const end = '2019-02-08T02:30:00.000Z';
MyModel.findOne({
    where: {
        timeRange: {
            $contained:
                [
                    new Date(start),
                    new Date(end)
                ]
        }
    }
});

This doesn't work and gets the error

error: operator does not exist: tstzrange = timestamp with time zone

The query looks like this

'SELECT * FROM "model" AS "model" WHERE  "model"."timeRange" = \'2019-06-26 22:00:00.000 +00:00\'::timestamptz;'

This probably explains why I got the PostgreSQL error. How can I properly format the query to get what I want?

user_3430463 · Answer

Using this similar question, I figured out a workaround but I'm not sure why or how it works : Query with date range using Sequelize request with Postgres in Node

var Sequelize = require('sequelize');
const Op = Sequelize.Op;
const start = '2019-02-11T00:30:00.000Z';
const end = '2019-02-08T02:30:00.000Z';
MyModel.findOne({
    where: {
        timeRange: {
            [Op.contained]: 
                [
                    new Date(start),
                    new Date(end)
                ]
        }
    }
});

If anyone has an explanation, I would be happy to hear it

How do I query a tstzrange with Sequelize?

1 Answers