HOW TO BUILD A REPORT THAT ACCURATELY REFLECTS DATA CORRESPONDING TO DIFFERENT LOCAL TIMES FROM DIFFERENT TIME ZONE --PART 2

Previously, I have stated the issue when it comes to reporting based on different time zone hours, now continue on what the solution I have found.

As previously mentioned "the conversion is giving the correct hour number from 0 to 23. However, when the result exceeds 23 (in the case of +8 time zone when adding 16 (UTC hour) with 8), it becomes 24 and it keeps going on with 8 more numbers by increment of 1; when the result is less than 0 (In the case of -8 time zone when adding 0 (UTC hour) with -8), it becomes -8 and it keeps going on with 8 more numbers by increment of 1."

So I have to add some conditions to take care of both cases when the number exceeds 23 and when it is less than 0.

As an experiment, I created one more column named 'hour conversion' with the following formula:

case when (hour(Date."Hour Date") + @{Offset}) < 0 then hour(TIMESTAMPADD(SQL_TSI_Hour, @{Offset}, Date."Hour Date" )) when (hour(Date."Hour Date") + @{Offset}) > 23 then hour(TIMESTAMPADD(SQL_TSI_Hour, @{Offset}, Date."Hour Date" )) else (hour(Date."Hour Date") + @{Offset}) end

The idea is that, when the converted time stamp is less than 0 and greater than 23, we want to display the converted time stamps in hour number, else, just the time stamp itself as it is in terms of hour number:

Now after done that, I have to create some filters on the report. The filter is going to base on the content of the formula defined in this column.

So, create a set of filter on date and "hour conversion" column:


1. cast(Timestampadd(SQL_TSI_HOUR, @{(Offset}, Date, "Hour Date") as date) is between date from the prompt ---- Here, I first convert the time stamp with Offset value, then convert it to date. Then use this to filter against the date values that user pass through the prompt.

2. case when (hour(Date."Hour Date") + @{Offset}) < 0 then hour(TIMESTAMPADD(SQL_TSI_Hour, @{Offset}, Date."Hour Date" )) when (hour(Date."Hour Date") + @{Offset}) > 23 then hour(TIMESTAMPADD(SQL_TSI_Hour, @{Offset}, Date."Hour Date" )) else (hour(Date."Hour Date") + @{Offset}) end is between the hours from the prompt --------- Here, basically whatever value that's converted by the formula in "Hour Conversion" column that I created will be compared against the hour number that user passes through the prompt:

Now, let's test it out:

This time, the time zone is UTC + 8 (Offset = 8).

As you can see, Hour Conversion field is showing the hour numbers matching Time Zone Local Time, the Hour Day Date column on the far right is matching the date as well

It works just as well with UTC -8 Time Zone

Now, let's change the filter hour to 1 to 3 and test again:

It is correct:

Remember the incorrect report from part 1 of this series? Now, let's compare that report with the new one and see the difference:

So, now I can confidently say that my report is going to give you the correct data at your local time based on whatever hourly criteria you want.

Thanks

Until next time