Issue with the timing in the timestamp in the Reports

Ignition
1

I created a Report where one of the column of the report is Time_Stamp the format for which is "yyyy-MM-dd hh:mm" now the problem I am facing is that when ever I schedule a report (for Ex. 4:00 ) the Time_Stamp should also start with time 4:00 and then after 15 minutes 4.15 and so on, but it starts with 4:07 and the 4:22 and so on. Please guide me is anyone had faced similar kind of problem and know the solution for the same.

2

What is the source of the data you're displaying in the report, and how are you querying it? Provide as much detail as possible.

3

I am fetching the data from from the SQL database where I have a column name as Time_Stamp.
I am quering the data like this--- SELECT BBFDwell.t_stamp,
BBFDwell.BarrelCountLast15Min
FROM BBFDwell
WHERE BBFDwell.t_stamp BETWEEN ? AND ?
ORDER BY BBFDwell.t_stamp
here the parameters are :-
StartDate -----dateArithmetic(now(), -24, "hr")
EndDate ------ now()
Hence I am displaying data for 24 hrs form current time to 24hrs back.
As you can see in this image this is how the data is displayed even when current time is 7:10.

image
image1920×1080 348 KB

4

There don't appear to be any date calculations in the query that generates the report - it simply selects the dates that are already stored in the database in BBFDwell.t_stamp.
All rows that were saved with a t_stamp between 2023-05-31 07:10:00 (local?) and 2023-06-01 07:10:00 will be included. BETWEEN is inclusive, so a t_stamp that exactly matches an endpoint will be included in the returned data.
How is the data generated? That is likely to be where you got 2023-05-31 07:37 instead of 2023-05-31 07:30 that you expected.

In general with timestamps

  • use UTC in the database and convert it to local time for display
  • if you need the current time in multiple places in the same query / sproc / script then you should set a variable to now() (or getdate() or whatever) at the top so that you don't have slightly different values throughout the script
    • In your above example, you won't get exactly 24 hours in your range - it will be a few nanoseconds more than 24 hours
    • If you are expecting exact matches on the endpoints and one endpoint is a few nanoseconds later then records will be included or excluded that you can't explain at the precision that the data is normally displayed.
  • If you want to calculate the number of barrels produced between 06:45 and 07:00 and you are calculating this and inserting based on a chron trigger at 07:00 based on now()
    • then... you are going to be disappointed. It isn't going to run exactly at 07:00.000000. It might run a few milliseconds later or it might run a few minutes later.
    • instead, do a bit of math on now() to round (floor) it down to the 15 minute interval and use that value for your calculations.
    • If you use BETWEEN 06:45 and 07:00 and then another calculation at 07:15 that is BETWEEN 07:00 and 07:15 and there is actually a barrel produced at 07:00 then that barrel will be counted twice - once in each group because BETWEEN is inclusive. Instead use t_stamp > @startDate AND t_stamp <= @endDate so that edge conditions are only counted once.
5

Most recommendations for half-open intervals are to use >= :start and < :end for best results with untruncated varying precision timestamps.

6

That makes sense, especially with the whole midnight 0:00:00 / 24:00:00 thing.

7

When using half-open intervals, it is very important to truncate your start and end timestamps to exactly the boundaries of the period, then the t_stamps can be examined as-is. When done this way, the results are perfectly reliable.

8

Yes. As I said. And then don't include both endpoints.

9

That's what "half-open interval" means. Developers from the database world (and math geeks) will recognize that phrase, and it's good to have it in the search terms for this topic.

10

Is the OP a database developer or a math geek?

11

Unclear. I try to cover the bases that might apply. And for future visitors. When a new person shows up and they have written some SQL, I tend to assume they have some exposure to databases.

12

Thanks for the suggestions guys but I just scheduled the time in Transaction Group for
Times: 0-24
Interval: 15 mins
Hence it will start for 00:00.
And I think its working just fine for now but still I would wait for 1-2 day to let the data populate and then see the results

13

I would say both

2 Likes