HELP - Pivot report displaying date in wrong timezone. Need to get my report out in 5 hours (5pm AEDT)

So, my app timezone is correctly set (to AEDT - Sydney). When the field techs are out there doing their job, the app timestamps the record via record rules.

So in my interface I have created a report page with some pivots and some graphs, and one pivot shows the work achieved by each technician each day.

The problem is that the date shown on the pivot table is equal to the date if it were in another timezone. NB: the date in the records of the database is correct with the correct timezone, it is just the pivot table labels that are wrong.

This means that the report is all wrong, showing work is occuring on the wrong days. And as the timezone factor changes at a time, it is not like the whole days work is appearing on the wrong day, it is spread across the days.

As the time itself is not shown, only the date, i need to manually review the numbers, and my best assessment is that it appears that the timezone that pivot table is using is somewhere in the US.


My theory is that the pivot table does not take into account the timezone setting and is using a default timezone. Of course, my alternate theory is that i am an idiot and have forgotten to set something somewhere (myapp timezone settings are set correctly)

Hi Guy,

Yes it is odd. Looking at my post above it has a "Pending approval” on the top right of my post !!
I have a support request open for this (#32502) and I'm advised "our engineers are still looking into this"

Another alternative I came across instead of ignoring time is to set the derived date field “Date Type” = hours (& Result Type = Date)

Equation = {LastUpdated} - 10
Because we set the date type to hours, the -10 subtracts the hours rather than days.

(N.B. depending on your data size it take approx 15 mins to replicate this to the actual record values. I wasn't aware of this initially which became extremely frustrating because during testing I was making changes which apparently didn't have an effect, so I made further changes every few minutes (before the data had a chance to update). So after a while I was seeing the results updating from a previous change thinking it was the most recent change before the results would change again and I didn't know why )

Unfortunately I still couldn't use the above equation because while the chart reports were now accurate, none of my pivots were accurate where they were using filters based on the original date field. Changing the pivot reports to filter by the derived field slowed the report/page generation to an unacceptable crawl.

Hopefully Knack can get this sorted sooner rather than later.

Hi Gavin

Odd, but your last comment has disappeared. It says there are 3 comments, so i am eliminating possibility that you deleted it.

Anyway, your comment is still in the email sent, so i will reply as if it is here.

Thanks for sharing your workaround. I agree that I prefer to avoid workarounds as they always have knock on effects, but often, they also where the art comes in and i do enjoy solving a challenge via one.

You workaround would achieve the purposes as isolated in my main post, however, by ignoring time it would then remove the 'real-time' functionality that the team leaders use (eyes on the field reps!).

As per your comments about the server updates etc, this is really the main point to my first post. It would seem to (totally under educated to comment) me, that this would be a widespread problem and one that would be easily solved. I certainly might have expected Knack to jump on it, or comment about the complexity of the matter. I might see if i can draw their attention to it again.

Hi Guy,

Thanks for the response. It's a shame you haven't had any input in that time.
Since my reply yesterday I came up with a workaround of sorts if you're interested.
I generally don't like workarounds but in this case it certainly kept me from having to export and massage the data in excel. Hope the following helps.

I created a new 'Equation' field, (mainly to test options)

Name = DerivedLastUpDate
Equation type = Date
Date Type = Days
Result Type = Date
Equation = {LastUpdated} (the [LastUpdated] field is a Date/Time field with simple date/time)
Time Format = Ignore Time

The only way I could get this to work was with the Ignore Time which derives only the date from the date/time and then build my charts around the derived date only field.

The strange thing was that before I started using the ignore time in my testing, I noticed there was some fluctuation in the derived times depending on if I tested between between 10-11am
i.e, around the time the server rolls over midnight (10-11am our time depending on daylight savings), all the derived times were 10 hrs early. This lasted just under an hour until after multiple refreshes the times eventually updated to the correct regional timezone times !! Maybe due to the backend DB taking time to update all equated fields where date/time are used !?

Either way, I hope the war & peace above in some way helps you or someone else that stumbles across this ;)
We just have to be very wary of how knack handles time.

Cheers, Gav

Hi Gavin

No. I have not received a response.

I have had to manually pull data and build excel reports as a result. The contract is therefore running at a loss, and the client isn't happy. Thankfully they love us for some other stuff!

Hi Guy,

Did you get any support or resolution on this ?

I'm also in Syd and have exactly the same issue with line charts reporting grossly inaccurate figures, not only with the date/time records but also when using the date filter " before today"

I noticed that the figures updated later in the morning at 10am which then also displayed the prior day's figures (coincidentally we are GMT+10).
This suggests the filters also aren't evaluating the app's timezone setting (and likely not the daylight savings switch either) .
It seems like our "previous" day's results are evaluated when the knack servers pass midnight (GMT) rather than midnight +/- timezone setting.

10am is too late as our GMs and state managers look at the data first thing in the morning.
It just looks unprofessional that the standard reports aren't taking timezone settings into account when reporting date/time data.