Configurable Timezone Display for Date Columns (Beyond UTC-only)

Tanguy

Current Situation

Dataiku DSS has specific behaviors when handling time columns:

• When it recognizes time-related columns (e.g., date, timestamp_tz, or timestamp_ntz), it displays them as Date columns, rendering them in timestamp format (with both date and time components).
• A significant limitation is that Date columns (which functionally serve as timestamps) are exclusively displayed in UTC.

Issues

This UTC-only display creates several challenges for our team (which is located in France):

1. Misalignment with Local Time: Despite setting the timezone in DSS datasets to our local timezone, the displayed time doesn't match our local time (see Figure 1). This creates confusion, especially since many users aren't aware of DSS's UTC-only display policy.
   
   
   Figure 1: Dataset timezone configuration having no effect on Date column display
   
2. Inconsistency with Other Tools: The time display in DSS differs from other databases (like Snowflake) that show times in local timezone, creating a disconnect in our workflow.
   
   
   Figure 2: Comparison showing timezone display differences between DSS and Snowflake
   
3. Seasonal Time Changes: France, like other European countries, observes daylight saving time:
   • Winter: UTC+1 (CET)
   • Summer: UTC+2 (CEST)
   This requires users to perform complex mental calculations when verifying dates (see Figure 3):
   • For example, with the date '2019-03-31':
     1. First, mentally convert to timestamp: '2019-03-31 00:00:000'
     2. Then convert to UTC:
        • summer time: '2019-03-30 23:00:000'
        • but other years that date can be in winter time: '2018-03-30 22:00:00'
   This is particularly problematic because:
   • The conversion varies by season
   • The displayed date can actually change (e.g., from 03-31 to 03-30), leading users to question data accuracy
     
     
     Figure 3: Illustration of user confusion when reconciling UTC-displayed dates
     

Workaround Attempts and Their Problems

Some users have attempted to solve this by changing their data timezone to UTC (see Figures 4.1 and 4.2), but this creates new issues:

• It results in inconsistent timezone values across datasets (some French, some UTC)
• It merely shifts the problem to other tools - when these datasets are accessed through other platforms (e.g., Snowflake), the timestamps appear incorrect from a local time perspective (see Figure 4.3)

Figure 4.1: (wrongly) Setting the session timezone to UTC (when, in our case, it should be 'Europe/Paris')

Figure 4.2: Dataset exploration with UTC timezone setting

Figure 4.3: Same dataset viewed in Snowflake with local timezone

Proposed Solution

To address these challenges, we suggest implementing:

1. User-Level Timezone Configuration: Allow users to set their preferred timezone for date display during their session
2. Node-Level Default Settings: Enable administrators to set default timezone settings at the node level, ensuring consistency across teams working in the same timezone

This solution would significantly improve user experience and reduce confusion, particularly for organizations operating outside of UTC timezones.

Turribeach

I agree this is complicated and not user friendly but I don't think your proposed solutions work in practice. The data the users see need to be in alignment with how it's handled in each of the backend data compute technologies (ie usually a database). You can not disassociate the compute with the display of the data. Otherwise the computation of those results could end in differences. Let's say you have a timestamp field which 14-Oct-2024 11pm UTC. If I need to show you that row in Central European Summer Time (CEST) which is UTC +2 that will result in 15-Oct-2024 1am CEST. If you are doing any sort of date calculation a different time zone can result in date differences. If your data doesn't use times you might be better off using the v12.6 feature to "write DSS dates (i.e. date+timestamp) into existing SQL tables with a SQL date (i.e. date only) type". That way you can avoid time zones altogether.

https://doc.dataiku.com/dss/latest/release_notes/12.html#id27