Dynamic date format- Dremio query

Hi- I would like give yesterday date dynamically while running the Dremio query.

My current query: SELECT * fromm “E-167”.“logstash-audit-2019.05.27”.logs

here, 2019.05.27 is the yesterday date. So can you please give me some dynamic query for this query without giving yesterday date value.

Like, Sysdate-1

Any help, greatly appreciated.

Hareesh Devarakonda

can anyone please help on this.

Try DATE_SUB() function: https://docs.dremio.com/sql-reference/sql-functions/datetime-functions.html

I had given the same answer, but then I note that date is a part of the table name xD.
we can do this on procedure, using some variable and exec command, but in dremio idk

@HAREESH, try promoting “E-167” as the dataset. You should get a column, dir0 with values that are logstash-audit- 2019.05.27, etc. If they all have the same naming convention, try removing the logstash-audit- part, convert the date and save this whole transformation as a VDS. Then do further operations on that VDS.

1 Like

Tutorial on curation: https://www.dremio.com/tutorials/data-curation-with-dremio/
Information about how Dremio treats partitioned datasets: https://docs.dremio.com/data-sources/files-and-directories.html#partitioned-datasets