How to know the current date in a sql query in kibana

[Diego_Benito]

Hi,

Im trying to execute the follow sql query:

GET _xpack/sql?format=txt
{
"query" :""" select level
from "default-*" where
queueName='ASPY_001_0200_RecogidaDatosGISS' and (transactionStatus='2' or transactionStatus = '3') and
timeStamp = "now-1d/d" """
}

I need the query to automatically extract the date every day, i dont know if its possible, but if is possible if someone can help me I appreciate it

[dadoonet (David Pilato)]

Isn't what is happening when you use now-1d/d?

[Diego_Benito]

If i run this query , i get the follow error:

[dadoonet (David Pilato)]

I see. Have a look at https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html#sql-functions-current-date

May be that would help.

[Andrei_Stefan]

@Diego_Benito Indeed, as @dadoonet already mentioned CURRENT_DATE and CURRENT_TIME should be used in this case. Also, you can perform date math operations using intervals, like in this example here.