Date time to date

yeswanththiyarir over 6 years ago

Hi,

I am trying to create a query entity. I have the following fields in the database: trans_date(datetime variable), trans_id(number),trans_amount(number). I am trying to write a query to return the sum of all transactions today. the only issue i am facing is to find the transactions today as trans_date is a datetime variable. I cannot filter this field using 'today' because type mismatch. I can only filter it with now() which results in 0 results as expected. I also tried field: todate("trans_date") which resulted in error.

Also, when I try to include trans_date in aggregation for grouping it only groups the transactions that happened at the exact same time as it is a date time variable.

Any help is appreciated

Thanks,

Yeswanth.

Discussion posts and replies are publicly visible

Parents

hrishikeshd0001 over 6 years ago

To the best of my knowledge, we cannot adjust the column value inside a query filter. I think the workaround in your case would be to use a "between" operator by providing timestamp values (beginning of the day, end of day).

a!queryfilter(
field: "trans_date",
operator: "between",
value: {
todatetime(today()) ,
addminutes(addhours(todatetime(today()),23),59)
}
)

That being said, this may work for a select query with query filter, but cannot be aggregated upon. The best solution is to create a view based on the existing table with desired column(s) (date column in this case) and then use query aggregation on this view.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Reply

hrishikeshd0001 over 6 years ago

To the best of my knowledge, we cannot adjust the column value inside a query filter. I think the workaround in your case would be to use a "between" operator by providing timestamp values (beginning of the day, end of day).

a!queryfilter(
field: "trans_date",
operator: "between",
value: {
todatetime(today()) ,
addminutes(addhours(todatetime(today()),23),59)
}
)

That being said, this may work for a select query with query filter, but cannot be aggregated upon. The best solution is to create a view based on the existing table with desired column(s) (date column in this case) and then use query aggregation on this view.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel

Children

Robert Shankin
Appian Employee
over 6 years ago in reply to hrishikeshd0001

This might work, but there's going to be Appian overhead involved with running that filter, containing those to functions.
Although it might seem tedious, your most efficient solution might be to make a view (or extend the existing view) that contains a DATE type column which you extrapolated from the date time. Then simply filter against the date and not the timestamp.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
yeswanththiyarir over 6 years ago in reply to hrishikeshd0001

Thank you hrishikesh for your solution.
Yeswanth.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
yeswanththiyarir over 6 years ago in reply to Robert Shankin

Thank you Rob.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel
hrishikeshd0001 over 6 years ago in reply to Robert Shankin

I agree with you Robert on that, hence i suggested it as just a workaround and mentioned the best solution is to go with the view approach. Thanks.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Cancel