Issue with last day of the month entry in a named query

Ignition
1

I have a monthly report set up in Ignition that uses a Named Query to populate daily totalized flow values. Since the totalized values are not written to the database until 1am of the next day, the Named Query is set up to log the values under the previous day's time stamp (see named query script below).

NamedQuery
NamedQuery933×494 45.5 KB

The Report page includes pull-down components for the Month and the Year. Everything is working like it's supposed to except for the fact that the data for the last day of each month shows up under next month's filter (see pic below where 8/31/22 shows up under September and 9/30/22 is missing since it shows up under October). Any help would be much appreciated.

Report
Report784×685 79.7 KB

2

Try,

WHERE 
    date_part('month', DATEADD(hour, -1, sand_totals_mor.t_stamp)) = :Sel_Month 
    AND
    date_part('year', DATEADD(hour, -1, sand_totals_mor.t_stamp)) = :Sel_Year

i.e., Offset all the t_stamps by one hour. That should work at month and year changes too.

Tip: post code using the </> button rather than (or as well as) screenshots to save us having to type it all out again!

3

Thanks!