Does time grouping only appears for question queries? I wrote a sql and created at data type was date type, but the one with questions worked with time grouping. other didn't
Time grouping works fine for SQL, assuming it is supported for your database type and you specify the filter correctly in the query.
I can attest it is working on PostgreSQL.
Post the queries that are working and are not and we’ll see if we can find the error.
WITH p AS (
SELECT
from_iso8601_timestamp(participants.createdAt) AS created_ts,
projects.interviewtype AS interviewtype,
participants.referrer AS referrer
FROM "datalake"."participants"
JOIN "datalake"."projects"
ON participants.projectid = projects._id
Where 1=1 [[AND {{interview_type}}]]
[[AND {{referrer_filter}}]] [[AND {{created_at}}]]
)
SELECT
date(created_ts) AS created_date,
interviewtype,
referrer,
count(*) AS total_participants
FROM p
WHERE created_ts IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1;
That’s how I am doing it
The time grouping variable needs to end up in a GROUP BY. I don’t see it in this query.
Its usually the format of:
SELECT {{time_grouping}} as ts, ...
...
GROUP BY 1
The filter will emit the necessary function calls to affect the time grouping for your database. On PostgreSQL its usually some variant of date_trunc().
WITH parsed_projects AS (
SELECT
projects._id,
projects.status,
{{created_at}}]] AS date
FROM "datalake"."participants"
JOIN "datalake"."projects"
ON participants.projectid = projects._id
WHERE 1=1
[[AND {{interview_type}}]]
[[AND {{referrer_filter}}]]
[[AND {{created_at}}]]
)
SELECT
date,
status,
COUNT(DISTINCT _id) AS total_projects
FROM parsed_projects
WHERE month IS NOT NULL
GROUP BY 1, 2
ORDER BY 1, 2;
There’s a typo in the query.
{{created_at}}]] AS date
You need to remove the trailing square brackets ]].
WITH parsed AS (
SELECT
COALESCE(
TRY(date_parse(participants.createdAt, '%Y-%m-%dT%H:%i:%s.%f')),
TRY(date_parse(participants.createdAt, '%Y-%m-%dT%H:%i:%s'))
) AS created_ts,
COALESCE(
TRY(date_parse(participants.updatedAt, '%Y-%m-%dT%H:%i:%s.%f')),
TRY(date_parse(participants.updatedAt, '%Y-%m-%dT%H:%i:%s'))
) AS updated_ts, participants.status
FROM "datalake"."participants"
JOIN "datalake"."projects"
ON participants.projectid = projects._id
WHERE 1=1
[[AND {{interview_type}}]]
[[AND {{referrer_filter}}]]
[[AND {{created_at}}]]
)
SELECT
date_trunc('month', created_ts) AS month,
SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) * 1.0
/ NULLIF(COUNT(*), 0) AS conversion_rate
FROM parsed
WHERE created_ts IS NOT NULL
AND updated_ts IS NOT NULL
GROUP BY 1
ORDER BY 1 ASC;
Can you please help where I am missing?
Make sure the dashboard filter type is set to type Date grouping or it won’t match the date grouping filter in the question.
Can you show me the configuration for the time grouping filter in the question?
SELECT
projects._id,
projects.status,
{{created_at}} AS date, COUNT(*) AS total_projects
FROM "datalake"."participants"
JOIN "datalake"."projects"
ON participants.projectid = projects._id
WHERE 1=1
[[AND {{interview_type}}]]
[[AND {{referrer_filter}}]]
[[AND {{created_at}}]]
GROUP BY 1, 2, 3
This is what I get. and I wanted date 
It looks like you have created_at set to a field filter so its returning a boolean value and not inserting the SQL necessary to implement the date grouping. You need 2 separate filters here.
Create a new filter, call it date_grouping, set it to type “Date grouping”, and replace the first use of {{created_at}} with it, like so:
SELECT
projects._id,
projects.status,
{{date_grouping}} AS date,
COUNT(*) AS total_projects
FROM "datalake"."participants"
...