How to insert where clause for dates using wix sql library

Ask the community
, , ,
1

Question:
How to insert where clause for dates using wix sql library

Product:
Wix Editor.

What are you trying to achieve:
I’m trying to perform a query using SQL, which returns data with a specific time period. As if it were
.ge(“data”, startDate)
.le(“data”, lastDate)

What have you already tried:
Using the library: ‘@velo/wix-data-sql-backend’
I’m trying to build the following query:

const query = `
            SELECT
                _id,
                sei,
            FROM MY_DATABASE
            LEFT JOIN SEC_DATABASE
                ON MY_DATABASE._id = SEC_DATABASE._id
            WHERE 
                  MY_DATABASE.data >= '${new Date('2024-01-02')}'
                AND MY_DATABASE.data <='${new Date('2024-01-03')}'
            LIMIT 100

However, this way all data is returned, ignoring the where condition.

Additional information:
It was done this way, as it makes it possible to use the left join and this has helped a lot.

2

When JavaScript coerces this value to a String it will be output like: "Mon Jan 01 2024 18:00:00 GMT-0600 (Central Standard Time)" which SQL can’t do a comparison on.

The easiest way to fix is just do MY_DATABASE.data >= '2024-01-02' without the string interpolation from JS.

If you need something programmatic then try (new Date('2024-01-02')).toISOString() or a date handling library like datefns to get the type of date format that this version of SQL supports which is probably a string like 2024-01-02 12:00:00.000 without the timezone information.

Note: .toISOString() includes the timezone information by default and not every SQL implementation handles this.

1 Like