Query a timestamp range in supabase

Question

I have a table in my supabase database with two columns start end end. Both are of type timestampz. Now I want to query all entries where the current timestamp (Date.now()) is inbetween start and end.

I thought I could do something like this (TypeScript):

var currentTimestamp = Date.now();
await serviceSupbaseClient
        .from("table")
        .select(
          "id, name, start, end"
        )
        .gte("start", currentTimestamp)
        .lte("end", currentTimestamp)
        .single()

But this does not work. I also tried formatting my currentTimestamp in the same String iso format that I get from the select.

Is this something that is not possible to do with postgrest supabase? Or what am I doing wrong?

Edit: This is my entry in my supabase db (start/end has different name.):

and my current Code is this:

var currentTimestamp = Date();
console.log(currentTimestamp);
await serviceSupbaseClient
    .from("lobby")
    .select(
      "id, name, valid_from, valid_to"
    )
    .lte("valid_from", currentTimestamp)
    .gte("valid_to", currentTimestamp)
    .single()

I get this error:

'invalid input syntax for type timestamp with time zone: "Fri Jan 26 2024 12:35:28 GMT+0000 (Coordina'

when using var currentTimestamp = Date.now();

I get this error: date/time field value out of range: "1706272571131"

Answer 1

You have the start time and end time in the wrong place. Your start time needs to be earlier than the current timestamp, and the end time needs to be later than the current timestamp. Flip your lte and gte, and you should see some data.

var currentTimestamp = new Date().toISOString();
const {data, error} = await serviceSupbaseClient
        .from("table")
        .select(
          "id, name, start, end"
        )
        .lte("start", currentTimestamp)
        .gte("end", currentTimestamp)
        .single()