hello. i have time series data in Druid and am usi...
# general
k
hello. i have time series data in Druid and am using Superset to chart that data. some charts look at the entire time series, but for some charts i only want to look at the last day's data. i can't configure the time range to be the "Last Day" or any of the other time range filters because it's possible i don't have data from "yesterday" in relation to "today". can someone help me with a custom SQL that would get the last day's (in the data, not in time) data for my chart?
an AI chatbot suggested this:
Copy code
SELECT *
FROM your_table
WHERE __time >= (
    SELECT MAX(__time)
    FROM your_table
    WHERE __time < CURRENT_TIMESTAMP
)
but i am not good with SQL and don't understand how this would help
a
You can try something like below:
Copy code
SELECT *
FROM your_table
WHERE __time >= 
FLOOR(TIME_SHIFT(SELECT MAX(__time)
    FROM your_table, 'P1D', -1) TO DAY)
This will give you all records from start of day of the prev day to the max timestamp in the table.
k
thanks for your reply. in the superset chart's custom sql box i put this:
Copy code
__time >= 
FLOOR(TIME_SHIFT(SELECT MAX(__time)
FROM "druid"."My Rawdata", "P1D", -1) TO DAY)
since superset assumes the stuff in front.
but i get an error:
Copy code
Error: druidException (INVALID_INPUT): Received an unexpected token [-] (line [7], column [65]), acceptable options: [<IDENTIFIER>, <HYPHENATED_IDENTIFIER>, <QUOTED_IDENTIFIER>, <BACK_QUOTED_IDENTIFIER>, <BIG_QUERY_BACK_QUOTED_IDENTIFIER>, <BRACKET_QUOTED_IDENTIFIER>, <UNICODE_QUOTED_IDENTIFIER>, "LATERAL", "(", "UNNEST", "TABLE"]
aslo, i realize now that i am in the Druid forum, not the Superset forum, so i will take this over to their slack channel. thanks for your help!