Appian Community and Appian Academy are being upgraded. From July 24–August 3, the Appian Community will be in read-only mode. During this time, the site will be read-only and user registration will be disabled. We apologize for any inconvenience this may cause, but a more secure, stable, and performant Community experience is coming soon!
The new Appian Community launches August 3, followed by Appian Academy on August 7. During the migration, Appian Community Edition, Appian Academy, Documentation, Certifications, Instructor-led Customer Training, Partner Sales Training & Accreditation, and Forum (for Appian Partners and Customers only) will remain available.
Hi,I kindly ask you for an opinion on a problem that I have been dragging along for some time.I have a query in which I need to extract data between two dates: startDate and endDate.The data on the db and CDT are in date and time format.The range extremes are entered from the user interface in Date format, the same are the query rule inputs.
a!queryFilter( field: "timestamp", operator: "between", value: { todatetime(ri!startDate),todatetime(ri!endDate) } ),
But when I do, for example, todatetime (12/21/2020), I get 12/21/2020 1:00 AM GMT + 01: 00 (in my local time zone).
So I can't actually take dates from the beginning of startDate=00:00 AM to the end of endDate= 11:59 PM.
I am trying this solution:
a!queryFilter( field: "timestamp", operator: "between", value: { todatetime(concat(tostring(ri!startDate), " 00:00 AM")), todatetime(concat(tostring(ri!endDate), " 11:59 PM")) } ),
Regards, and thank you very much
Discussion posts and replies are publicly visible
You can't use the todatetime() function with multiple parameters or it will assume you want a list of datetime. You should look at the datetime() function which will let you create your own datetime more easily. As another approach, you can look into using two a!queryFilters with the greater than or less than operators
Hi Danny,
taking into account your suggestion on using datetime() function, I think a better solution for me is using the following date filter:
value:{ gmt(datetime(year(ri!startDate), month(ri!startDate), day(ri!startDate), 0, 0, 0 ) ) , gmt(datetime(year(ri!endDate), month(ri!endDate), day(ri!endDate), 23, 59, 59 ) ) }
This solution is valid if we assume that the writing of the dates is done by Appian because of the handling date and time.
https://community.appian.com/w/the-appian-playbook/983/handling-date-and-time
Thank you
Regards
what i've always done is just added 1 day to the end date (since there's not even a millisecond of overlap time when all is said and done). When querying from the database you need to be careful because I think Appian automatically does some conversion back from GMT when using a passed Appian date or dateTime object.
i.e.
value: { todatetime(ri!startDate), todatetime(ri!endDate + 1) }
OK Mike, I also always used to add 1 day to the end date but in some cases it happened to lose a data that was instead included in the range or to add one that shouldn't have to appear after the query. So I focused my attention on finding an alternative solution.
I don't believe that's likely given what I suggested above - quite literally what it will query is ("anything from the beginning of startDate") until ("end of the day on endDate"). The only possible extra adjustment that would be needed is ensuring your values are playing properly with GMT, and that's pretty easy to test by adding some test entries to the DB with times very near midnight (some just before and some just after) to make sure they come up in the expected query.
Hello Mike, I too have always done as you do!
But could you please explain a little bit more what you meant by "since there's not even a millisecond of overlap time when all is said and done" ?
Thanks in advance, regardsPaolo
A datetime stored in the DB is actually stored as a number that, as far as I remember, is accurate to the millisecond (and if that's wrong, it's at least accurate to the second). Basically that means that when you want to query "everything for X date" for a column that stores a dateTime, it should always be sufficient to query between "Midnight of Date X" and "Midnight of Date X+1" (as opposed to bending over backwards to compare against "Date X at 23:59:59" specifically).