• Replies 7 replies
  • Subscribers 11 subscribers
  • Views 10207 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Best Practices

Date time to date

yeswanththiyarir

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
  • 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.
  • Appian Employee
     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.

Reply
  • Appian Employee
     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.

Children
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.