Josh Peak
05/07/2025, 7:33 AMdim_date
which has a column week_start
for each row which is a date.
• For example, the week_start
is 2025-04-28
as the raw value in Snowflake for dates between 2025-04-28 :: 2025-05-04
since the week starts on Mondays
• week_start
is a Snowflake Date
type.
🐞 Problem:
In GoodData it renders as 2025-04-27
which is off by a day
Triage steps provided in thread 🧵Josh Peak
05/07/2025, 7:35 AMonyxgaming
account in this channel here:
https://gooddataconnect.slack.com/archives/C04S1MSLEAW/p1746399328777779
I am a consultant working on the Onyx Gaming Good Data platform as wellJosh Peak
05/07/2025, 7:36 AMJosh Peak
05/07/2025, 7:37 AMJosh Peak
05/07/2025, 7:41 AMweek_start
for each of the dates has the correct underlying value.Josh Peak
05/07/2025, 7:45 AMSELECT
*
FROM snowflake.account_usage.query_history
WHERE
user_name = '<insert prod service account name here>'
-- filter for queries that reference the table we use
and lower(query_text) like '%dim_date%'
ORDER BY start_time DESC -- most recent
LIMIT 10;
Josh Peak
05/07/2025, 7:46 AMJosh Peak
05/07/2025, 7:47 AMJulius Kos
05/07/2025, 9:11 AMJulius Kos
05/07/2025, 10:04 AMDATE_TRUNC()
or DAYOFWEEK()
?
• Did you create or modify any custom date tables or views that handle week calculations?Josh Peak
05/07/2025, 10:08 AMDate
to a unix epoch timestamp shows that the raw value is:
1745798400
Which is 28th April 2025 000000
These are timezone NON-aware dates.
My guess is:
• the Snowflake SESSION attribute adds the Melbourne/Australia timezone
• So 28th April 2025 000000 GMT+10:00
• Becomes 2*7*th April 2025 140000 GMT+00:00
• Then cast to a Date
• And the GoodData engine is ignoring our timezone settingsPavel Cerny
05/09/2025, 12:46 PMweek_start
and also attribute week_start
in dataset DIM_DATE
. Is there any reason for that? Be aware that week_start
should be modeled as date dimension and then used in report as date dimension with e.g. day precision.
Your reproducer works with week_start
as attribute and that have consequences. One of them is, that week_start is returned from DB as Date data type. When we fetching data from DB, we consider date with Melbourne TZ and convert it to UTC. That is a bug on our side.
If you replace week_start
as attribute by week_start
as date dimension, you will get correct behavior. If you check produced query after the change, you will see that date is converted to chart at the end so our fetching routine works with it as string, i.e. no TZ shifts.
By the way, there is an option to ask platform for the report explain. Explain contains also SQL query to construct the report. Simply go to analytical designer, open report and change last part of url from /edit
to /debug
.Josh Peak
05/11/2025, 3:08 PM/debug
option on the analytical designer. Thank you very much.Joseph Heun
05/12/2025, 2:55 PMJosh Peak
05/14/2025, 6:10 AM