Ubidots Community

[SOLVED] Reporting daily totals - How to handle the midnight issue

Data management
,
#1

Need some advice and I cannot be the only one who has faced this issue.

I have a device that monitors a pump and counts the number of minutes it has pumped each day. It reports this number every hour and - importantly - the data is transmitted and recorded by Ubidots within about 5 seconds. At midnight, the pump minutes count is reset and we begin the next day’s count. The pump only pumps every 2-3 days in the winter.

The issue comes when I run the reports for the week. I use the Ubidots reports engine and report the “Max” count for a “1 day” period. The issue is that, this can lead to misleading reporting as the count for day 1 - say 50 - is counted as the “max” for day 1 as expected but also as the “max” for day 2 since it was received by Ubidots at 5 seconds after midnight. This is not correct.

I am not sure how best to correct this as I want to keep my code simple and I want to use the standard Ubidots reporting tool.

  • I could zero the count after the 2300 period but this would not be correct if there is pumping activity after midnight
  • I could start the reporting process before the top of the hour but this makes the code more complex as I cannot always predict cellular connection / reporting times.
  • I could manually fix this issue but, in the summer when the pump can go every day, this would be hard to spot.

Any best practices or advice?

Thanks, Chip

#2

Hi there @chipmc, can you share your report’s label so I can check it?

I will be attentive

#3

Sure, “Falls Lake Report”.

Chip

#4

Hi Chip,
Since the timing of events in each independent system (device, and cloud) will never be synchronized, would it be possible to introduce and log an indicator of period that originates in the device that is doing the counting? The indicator could be the julian date day for instance and would be incremented in the code at the same time as the counter rollover.

This would give you an additional qualifier during reporting that could exclude late data. Now the query is (Max Val WHEN Period indicator = Target Period), both of which originated in the same system. Then from the Ubidots perspective, the selection of Day just indicates the Target Period value, not the precise value of the system timestamp.

Not sure if the above can be implemented within the standard reporting framework. Regardless at minimum, you must decouple the report execution moment from the end of reporting window - the data will always be late, so why fight the timing :slight_smile:
Cheers, Sam

1 Like
#5

Sam,

Agree, there is no way around the fact that connecting and reporting will take time that extends outside the reporting period. I can think of a few ways to resolve this but am hoping for some advice on the best approach:

  • Ubidots could support custom time periods (perhaps defining a period as x hours / y minutes or extending for 24 hours but starting at 5 minutes after the hour.
  • I could include a timestamp in the JSON payload (like you suggest) and Ubidots reporting could allow report generation based on timestamp in addition to today’s “when received” approach.
  • Perhaps there could be a “cleanup or data transformation” step using UbiFunctions that would align the data reporting to the hour and then store it in the device variable.

Just looking for the least bespoke and complex way to do this.

Thanks, Chip

#6

Hi @chipmc, I looked into your report and you set it to be sent every monday in a time span of last seven days, this time span is inclusive, so I think that if you set it to last week you will be able to retrieve properly the data from monday to sunday for your report.

Let me know how it goes

#7

@jotathebest,

Makes sense, I will try that.

Any advice on the midnight issue?

Chip

#8

Hi Chipmc, my apologies for this tardy response, certainly sometimes my daily workload does not allow me to be active as I’d want in the community. I understand that it may look like an issue, but it is not. If you select last xxx days/months as time span will always include from now to the past, because that time span includes the actual date; because of this we have other quick date range like yesterday or last week that do not include it. Does this make sense for you?

Btw, I will pass your feedback to our PM, so we can search alternatives ways to make our reports module more intuitive for our users.

Thanks

1 Like
#9

@jotathebest,

Thank you - but what about the Midnight Issue?

Thanks, Chip

#10

@chipmc,

I have a similar run time counter on a Particle device that sends the run time in hrs as a variable titled s_fan_hr to Ubidots.

Then on the Ubidots side I created a synthetic variable that I title something like "Fan Run time (24H) and use the following synthetic expression: sum(s_fan_hr,“24H”).

Could this help with your Midnight Issue?

#11

@Backpacker87,

Thanks for responding. I am not sure this will solve the issue but it would certainly be worth a shot. The issue in your approach would not be in the number - as your approach will capture that. The question will be if the synthetic variable is posted 5 seconds after midnight, will the Ubidots reporting model assign that value to the correct day in the report.

Still, worth a try and thanks again!

#12

Hi @chipmc,

Yes, synthetic variables, specially those of the Data range type, just as the one proposed by @Backpacker87, always have a “fixed” timestamp based on the range. For a 24h summation, the timestamp will be placed (always) at the beginning of the day, that is, every day at 00:00:00. It is not dependent of the module’s computation time. However, if data arrives even seconds after midnight, it will be counted as a value for next day’s summation, or the report Max of day computation which what you would like to avoid. To prevent the last Dot of the day from being included in next day’s Max calculation, I think the best approach is sending the timestamp from the devices, or at least only for the last hour of the day so it is a few seconds before midnight.
Makes sense?

–David

#13

@dsr,

This turned out to be the best solution. I added a timestamp to each webhook and used the Particle integration to add it to every variable so the Particle device only sent the data once over cellular. Now, all my “dots” have a x:59:59 timestamp and so the “max” values are for the correct day.

Thank you,

Chip

1 Like