Splunk AppDynamics

How to put a date and time condition in a ADQL statement

Tausif_Ahmad
New Member

How can I put the current date in the where clause? For example with the below query I want to fetch all IDOCs that has been created today. I have just hard coded today's date. What should I use to put the today's date condition?

SELECT CREDAT, DOCNUM, STATUS, MESTYP, TIMESTAMP FROM idocs_details WHERE MESTYP = "ZPSWDMGMT" AND CREDAT = "20220324" anD STATUS = "51"

Labels (1)
Tags (1)
0 Karma

Kenji_Kumada
Path Finder

Hi @Tausif.Ahmad

Thank you for your post to the community!

If your environment is a Saas environment, one option is to use "now" function.
https://docs.appdynamics.com/appd/21.x/21.5/en/analytics/adql-reference/adql-queries/analytics-funct...

This function returns a datetime value. From this value, you can create another datetime value for the current date at 00:00, and use it in "WHERE" clause. This is an example:

SELECT eventTimestamp, application, toString(eventTimestamp, 'yyyyMMdd-HH:mm') AS eventTimestampString, toString(now(), 'yyyyMMdd') AS currentDateString, toDate(currentDateString, 'yyyyMMdd') AS currentDate FROM transactions WHERE (eventTimestamp - currentDate) > 0 LIMIT 100

image.png

Note that comparing two datetime fields or comparing a datetime field and a returned value by "now" function may not work. But you can use the result of subtraction for a condition as in the example.

If your environment is not Saas, unfortunately, there is no straightforward way to do this. It may be possible to use "SINCE ... UNTIL" clause or "series" function but it depends on the data. And probably it may require some data manipulation. Here is the documentation for "SINCE ... UNTIL" clause and "series" function.

https://docs.appdynamics.com/appd/21.x/21.1/en/analytics/adql-reference/adql-queries/since-until-cla...
https://docs.appdynamics.com/appd/21.x/21.5/en/analytics/adql-reference/adql-queries/analytics-funct...


Hope this answer helps!

Best regards,
Kenji

Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...