https://www.gooddata.com/ logo
Join Slack
Powered by
*Date Formatting Issues for Date Attributes* :mag:...
# gooddata-cloud
j
Date Formatting Issues for Date Attributes 🔍 Context: • We have a dbt model called 
dim_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 🧵
This is further to the issues already raised for the 
onyxgaming
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 well
• We have changed the First Day of Week setting in Snowflake and GoodData at the Account and Workspace levels to be Monday. • We have changed the timezone from UTC to Melbourne in Snowflake and GoodData at the Account and Workspace levels
Here is the underlying snowflake data. As you can see the 
week_start
for each of the dates has the correct underlying value.
Went spelunking through our Snowflake query history to see what exact query good data was executing:
Copy code
SELECT 
    *
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;
Which showed the underlying query that GoodData executed got the correct value.
So the issue is somewhere: • from when the query is returned • to pixels on screen It seems limited to using this column date as an attribute when converting the date to a string.
j
Hi Josh, Thank you for the thorough review and detailed description of the issue. Much appreciated. Based on the information provided, it seems likely that the First Day of Week setting at the Snowflake level is causing the issue. Ideally, this setting should only be configured in GoodData. Would it be possible for you to adjust this in your source data? Unfortunately, there’s a strong possibility that we don’t fully support or correctly process this setting from Snowflake. However, we will double-check to confirm this. I also suspect that this setting might be the root cause of the second issue reported in the other thread. We will get back to you once we have more information.
Also, few more questions here. You’ve mentioned changing the “First Day of Week” setting in Snowflake. Can you clarify how this was implemented? • Was it a configuration or parameter change at the account/session level? • Did you adjust the week start logic within specific date functions like 
DATE_TRUNC()
or 
DAYOFWEEK()
? • Did you create or modify any custom date tables or views that handle week calculations?
j
Casting the 
Date
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 settings
👀 1
p
Hi Josh, we have noticed that logical model contains date dimension 
week_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
.
👀 1
j
Thanks for confirming the bug in how the attribute is being handled. That is what we suspected. As you can see from our triage it took a lot of "surely we have stuffed up a timezone setting somewhere" which would have been the mostly likely cause 99.99% of the time. We have already progressed to trying alternatives to get this to work and we will use the date type and see how that pans out. From our reference test it looked like it had the correct behaviours. That is a handy tip to know about the 
/debug
option on the analytical designer. Thank you very much.
j
Hi Josh and Onyx Team, While our engineers are working on a fix for this, we would like to point out that there is no real benefit in using date as an attribute. The standard way is to use date as date dimension. When you update your model and change the report to use date dimension the issue disappears. Therefore, we believe there is currently a viable workaround for the solution. Nevertheless, we will certainly keep an eye on the progression of the fix with the engineers and keep you posted with any new details as they emerge. You can follow our product updates on our release notes posted HERE for any additional product updates.
j
Can confirm that after updating the mappings for the LDM removing it as an Attribute, then creating a WEEK_START Date in the LDM that maps to the field allowed us to achieve the following in the screenshot which has remedied the situation.
j
Hi Josh, The release has finished and the issue with the Incorrect Timezone conversion should now be fixed. Could you please verify this from your end?
4 Views
Open in Slack
PreviousNext
Powered by