- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Autogenerate while loop with hourly increment
I'm trying to generate hour-by-hour timestamps for each hour between two dates.
To do this, I take a functional script for doing the equivalent problem, which instead of hour-by-hour, it generates a timestamp for each day between the two specified dates.
This results, as expected, with this:
I attempt to amend this script to have an addition of hours rather than days:
However this results in an unusually long load time, that never seems to end:
Since it is a while loop, the terminating condition would be a candidate for the problem. However making test variables and checking the result of adding hours does indicate this process does work in isolation:
Results in:
As expected.
Why is the addition failing in the while loop, causing it to never terminate?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I figured it out, and I'm posting the solution here just incase somebody finds it relevant to their work.
The problem is that I was adding a "Time#(IterNo() - 1, 'h')", which indeed increments the hour, for some reason it is not designed to be able to add to a given day to progress to the following day. To illustrate:
- 29/08/19 22:00 + Time#(1, 'h') evaluates to 29/08/19 23:00, however
- 29/08/19 23:00 + Time#(1, 'h') evaluates to 29/08/19 00:00
The way to remedy this is to perform the addition using the interval function instead. This will elicit the required results:
- 29/08/19 22:00 + Interval#(1, 'h') evaluates to 29/08/19 23:00, and as required,
- 29/08/19 23:00 + Interval#(1, 'h') evaluates to 30/08/19 00:00
When the 'interval' function is placed in the while condition, the loop will terminate as required.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I figured it out, and I'm posting the solution here just incase somebody finds it relevant to their work.
The problem is that I was adding a "Time#(IterNo() - 1, 'h')", which indeed increments the hour, for some reason it is not designed to be able to add to a given day to progress to the following day. To illustrate:
- 29/08/19 22:00 + Time#(1, 'h') evaluates to 29/08/19 23:00, however
- 29/08/19 23:00 + Time#(1, 'h') evaluates to 29/08/19 00:00
The way to remedy this is to perform the addition using the interval function instead. This will elicit the required results:
- 29/08/19 22:00 + Interval#(1, 'h') evaluates to 29/08/19 23:00, and as required,
- 29/08/19 23:00 + Interval#(1, 'h') evaluates to 30/08/19 00:00
When the 'interval' function is placed in the while condition, the loop will terminate as required.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Only problem for was that the loop would end one hour short.
For example, if vToday = 21/12/2021 08:00:00, then the max timestamp in the AddingHours table outcomes to 21/12/2021 07:00:00. I assume this is because of the interval function.
Simply corrected it with
... <= ($(vToday) + time#(1, 'h'))
as indicated in your original post.
Thanks a lot for this!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
This is a really useful thread, I found a slightly less complex way of calculating the same thing:
Let v_date_full = Timestamp(Today() - 1);
[Date_Table]:
LOAD
Timestamp(('$(v_date_full)') + Interval#((IterNo()-1), 'h')) as [timestamp]
AutoGenerate 1 While IterNo()<=24;
Result:
Hope this is helpful.
Thanks!