cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

Tausif.Ahmad
Creator

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"

1 REPLY 1

Kenji.Kumada
AppDynamics Team

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

2022-09-08-15-50-14.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

On-Demand Webinarr
Discover new Splunk integrations and AI innovations for Cisco AppDynamics.


Register Now!

Observe and Explore
Dive into our Community Blog for the Latest Insights and Updates!


Read the blog here