# Issue with the timing in the timestamp in the Reports **URL:** \ **Category:** Ignition\ **Created:** [May 31, 2023, 9:50pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399 "2023-05-31T21:50:44Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![R\_Darji](https://avatars.discourse-cdn.com/v4/letter/r/f05b48/32.png) [@R\_Darji](https://forum.inductiveautomation.com/u/R_Darji)\ **Post date:** [May 31, 2023, 9:50pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/1 "2023-05-31T21:50:44Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [May 31, 2023, 10:25pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/2 "2023-05-31T22:25:29Z")
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. ---
**Author:** ![R\_Darji](https://avatars.discourse-cdn.com/v4/letter/r/f05b48/32.png) [@R\_Darji](https://forum.inductiveautomation.com/u/R_Darji)\ **Post date:** [June 1, 2023, 12:13pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/3 "2023-06-01T12:13:03Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/b/fbf653df7ad34c2e48a3836915b17709f0bccea0.png) ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [June 1, 2023, 1:07pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/4 "2023-06-01T13:07:22Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 1, 2023, 1:10pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/5 "2023-06-01T13:10:26Z")
> [@Tim\_Carlton](#): > > Instead use `t_stamp > @startDate AND t_stamp <= @endDate` so that edge conditions are only counted once. Most recommendations for half-open intervals are to use `>= :start` and `< :end` for best results with untruncated varying precision timestamps. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [June 1, 2023, 1:13pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/6 "2023-06-01T13:13:35Z")
> [@pturmel](#): > > Most recommendations for half-open intervals are to use `>= :start` and `< :end` for best results That makes sense, especially with the whole midnight 0:00:00 / 24:00:00 thing. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 1, 2023, 1:17pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/7 "2023-06-01T13:17:50Z")
> [@Tim\_Carlton](#): > > That makes sense 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. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [June 1, 2023, 1:23pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/8 "2023-06-01T13:23:03Z")
> [@pturmel](#): > > truncate your start and end timestamps to **exactly** the boundaries of the period Yes. As I said. And then don't include both endpoints. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 1, 2023, 1:24pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/9 "2023-06-01T13:24:05Z")
> [@Tim\_Carlton](#): > > don't include both endpoints. 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. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [June 1, 2023, 1:27pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/10 "2023-06-01T13:27:36Z")
Is the OP a database developer or a math geek? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 1, 2023, 1:28pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/11 "2023-06-01T13:28:52Z")
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. ---
**Author:** ![R\_Darji](https://avatars.discourse-cdn.com/v4/letter/r/f05b48/32.png) [@R\_Darji](https://forum.inductiveautomation.com/u/R_Darji)\ **Post date:** [June 1, 2023, 1:39pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/12 "2023-06-01T13:39:37Z")
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 ---
**Author:** ![R\_Darji](https://avatars.discourse-cdn.com/v4/letter/r/f05b48/32.png) [@R\_Darji](https://forum.inductiveautomation.com/u/R_Darji)\ **Post date:** [June 1, 2023, 1:40pm UTC](https://forum.inductiveautomation.com/t/issue-with-the-timing-in-the-timestamp-in-the-reports/74399/13 "2023-06-01T13:40:08Z")
I would say both