Finding all rows in range of two dates

[yustarandomname]

I would like to query all rows between timestamp1 and timestamp2 I have tried this:

const date1: Date = new Date();
const date2: Date = new Date();
date2.setDate(date2.getDate() + 1) // just for example so that we do not have the same date twice

const dateString1: string = date1.toISOString();
const dateString2: string = date2.toISOString();

const {data} = await supabase.from('News_items').select('*').rangeLt('publish_date', [dateString1, dateString2])

However this will complain that [dateString1, dateString2] is of type string[] and not string. Furthermore the console is throwing this error hint 'No operator matches the given name and argument types. You might need to add explicit type casts.', "operator does not exist: timestamp without time zone << unknown"

---

The docs here gives this example:

const { data, error } = await supabase
  .from('countries')
  .select('name, id, population_range_millions')
  .rangeLt('population_range_millions', [150, 250])

What would be the valid way of finding all rows between 2 date?

[GaryAustin1]

You should be able to do it with chaining .gte, .lte.
Edit: It is my understaning .rangeLt compares a range type against another, versus a single value, but I'm not that familiar with PostGres ranges and the translations postgrest.js does on converting .rangeLt to postgrest API call.

[yustarandomname]

supabase.from('News_items').select('*')
      .lt('publish_date', dateString2)
      .gt('publish_date', dateString1);

yeah this works but I doesn't look right, in theory should this be possible with 1 range filter

[GaryAustin1]

I just edited my response. My interpretation is that rangeLt operates on two range types, not a single value type.

@steve-chavez, I don't find any real clear explanation of supabase/postgrest.js use of rangeGt, Lt type filters and what exactly they do for range types versus single value against a range. Can you point to anything besides the basic docs postgrest/postgres range section?

[steve-chavez]

'No operator matches the given name and argument types. You might need to add explicit type casts.', "operator does not exist: timestamp without time zone << unknown"

The rangeLt and other range filters only work on range types(and publish_date is not a tsrange but a timestamp), that's why you get the above error message(which is trying to use the << operator, that's only available for ranges, rangeLt = <<). Using an lt and gt seems apt for your use case.

I don't find any real clear explanation of supabase/postgrest.js use of rangeGt, Lt type filters and what exactly they do for range types versus single value against a range

Hm, seems like the docs are wrong, a range value should be used like this.

[GaryAustin1]

wow... @steve-chavez all my coding sensibilities would barf at '[2,25)' but I don't see anything like that in postgREST so I assume a SB thing? Ranges must not be used much or it is late, and I should reread in morning... Thanks for responding!
Edit, I learned/relearned something new on [ x,y) notation for ranges.

[forntoh]

How can I do it in .or() filter, it gives the same error

    let query = supabase.from(table).select();
    if (username) query = query.eq("username", username);
    if (start && end) {
      const a = format(end, "yyyy-MM-dd");

      query = query
        .gte("periodStart", start)
        .or(`periodStart.lte.${a},periodEnd.lte.${a}`);
    }

[kosmasfn]

I try to get data with a range 'startDate' & 'endDate'

          `  val tasks = postgrest.from("tasks").select {
                filter {
                    gte("created_at", startDate)
                    lte("created_at", endDate)
                    eq("email", email)
                }
                order(column = "created_at", order = Order.DESCENDING)
            }`

But, not working as expected. This query will return all data "greater than startDate" or "less than endDate".
How to get data with a range time? I expect the query not using "OR" but using "AND" query filter.
Anyone ever experience using this query?