Date Parse in Query JSON with sql

Queries and Resources
1

Hi! im trying to parse a date in query json with sql in a normal way and its failing:

and tickets.created_at::date >= {{ dateRange1.value.start }}

due to the original query, i cannot parse the data in the original query and need to do it here

The data comes in looking like this: created_at
"2024-01-14T23:21:18Z"

2

Hi @Tommy_Fiacco - welcome to the community!

Try adjusting your initial SQL to be like this:

AND TO_TIMESTAMP(tickets.created_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')::date >= {{ dateRange1.value.start }}

In this query, TO_TIMESTAMP function is used to convert the created_at string to a timestamp with the specified format. Then, the ::date operator is used to extract the date part from the timestamp.

Let me know if that works for you

3

i dont think to_timestamp is supported in query json with sql, and i dont have a way of adding it to the initial query easily

4

What SQL engine do you have?

I did not test the code chunk I sent you and got this answer from ChatGPT. I do believe TO_TIMESTAMP is part of SQL (Postgres engine at least). I could not quicky find it for MySQL engine that's why I'm asking.

5

QUERY JSON With SQL resource in retool

6

I see. According to the docs, Query JSON with SQL resource uses AlaSQL and its syntax differs from SQL.

I may try to come up with something

7

Since you have ISO date string then simple string comparison should work just fine:

select * from {{ [{ id: 1, created_at: "2024-01-14T23:21:18Z" }, { id: 3, created_at: "2024-01-10T23:21:18Z" }] }}
where created_at >= {{ dateRange1.value.start  }}

Note that dateRange1.value.start is not exactly an ISO string. But it works - see screenshot:

CleanShot 2024-01-17 at 1 .15.49@2x
CleanShot 2024-01-17 at 1 .15.49@2x2028×1416 236 KB

8

Hi @Tommy_Fiacco ! Dealt with what I'm assuming is a similar problem a bit ago. Here's what might work for you:

Goal: filter for all tickets with created_at date that comes after {{dateRange1.value.start}}

Suggestion: and date(tickets.created_at::date) >= {{ dateRange1.value.start }}

My data comes in as captured_at: "2024-01-13T00:00:13.549Z", and using

WHERE date(status.captured_at) <= (CURRENT_DATE) and date(status.captured_at) > (CURRENT_DATE-7)

allows me to filter out a 7 day range from a given date, so should work in your case as well (given that CURRENT_DATE returns 2024-01-18, so i'd assume your dateRange1.value.start is in the same 'YYYY-MM-DD' format).

Have worked a lot with filtering dates & datetimes, so happy to chat more if needed

Also, like @preshetin said, Retool uses AlaSQL, so poking around their docs might be helpful as well

Hope that works!

2 Likes