daterange column input is modified before writing to db

[kyeotic]

Bug report

Describe the bug

When writing to a daterange column with a range like "[2022-12-28, 2022-12-28]" the column is modified to look like "[2022-12-28,2022-12-29)".

There are two problems.

1. The end date has been incremented by one day
2. The upper bound of the range has been modified from exclusive to exclusive

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Create a table with the following schema

   
   CREATE TABLE periods (
   id              text        PRIMARY KEY DEFAULT uuid_generate_v4(),
   dates           daterange   NOT NULL,
   EXCLUDE USING gist (dates WITH &&)
   );

2. Try to update it with the following code

```js
const { data, error } = await this.supabase
      .from('periods')
      .insert({
        dates: "[2022-12-28, 2022-12-28]",
      })
      .select()

Look at the returned data, or the data in the database, and see that the range has been modified.

Expected behavior

The input should not be tampered with. If the input is invalid an error should be thrown.

Screenshots

Here is a screenshot of the debugger, showing a function that happens to be taking in the same date twice.

You can see in the watch panel that the demo value is the same one sent to supabase.insert, and that the data result in the watch panel is not the same.

System information

• Version of supabase-js: 2.2.2

Additional Context

If you enter this value into the web UI postgres interprets the range as empty. This seems like a valid result, but I am not sure if the webUI is just making a different modification.

[soedirgo]

Hey @kyeotic, [2022-12-28, 2022-12-28] is equivalent to [2022-12-28, 2022-12-29) - the latter is just how Postgres decides to display the value. You can try running this on the SQL editor:

select '[2022-12-28, 2022-12-28]'::daterange

The two problems negate each other - if only one is present, the value would be incorrect.

In general, you can't expect the original text formatting of values to be preserved - you'll need a separate text column for that.

[kyeotic]

@soedirgo Thank you for the response!

I don't expect the values to be preserved as entered, I do want them converted into a range because I am doing range-based queries on the data. However, this statement is not correct

the latter is just how Postgres decides to display the value

The value is being modified clientside before being sent to postgres. You can see this in the screenshot, which shows the data in the body of the HTTP request being sent. This modification produces an error.

The Web UI also modifies the input, but it modifies it to empty. This modification does not produce an error.

These behaviors are not aligned, and one of them produces an error. I believe this constitutes a bug in the JS library. If the JS library behaved like the Web UI and sent empty instead, the request would succeed.

[soedirgo]

Are you sure it behaves differently on the dashboard? I can't seem to reproduce that (same result using the Table Editor):

select '[2022-12-28, 2022-12-28]'::daterange as same_day_inclusive, '[2022-12-28, 2022-12-29)'::daterange as next_day_exclusive, '[2022-12-28, 2022-12-28)'::daterange as same_day_exclusive;

| same_day_inclusive      | next_day_exclusive      | same_day_exclusive |
| ----------------------- | ----------------------- | ------------------ |
| [2022-12-28,2022-12-29) | [2022-12-28,2022-12-29) | empty              |

There shouldn't be any client-side modification happening in the library.