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.