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!

Index This | What did the zero say to the eight?

June 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

Splunk Observability Cloud's AI Assistant in Action Series: Onboarding New Hires & ...

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

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...