Apologies, in advance, for writing a book here. This is a complex problem with a somewhat inelegant solution that I don't expect anyone to quickly follow without explanation.
First, consider the fact that there are only 14 unique calendars, and the calendar to use can be algorithmically determined based on a given date's year.
I'll mention that I have never approached this problem before now and had trouble finding even a generic (as opposed to SAQL-specific) solution, but of course it exists -- timezone configuration, though glaringly absent in Einstein Analytics, is basically a ubiquitous feature of modern software offerings.
There is a SOQL function that converts UTC to the user-configured time zone (which I assume includes DST handling), and I've found some use cases where SOQL functions are allowed in SAQL expressions. In this case, however, I couldn't get around undocumented errors, mainly Function doesn't have a type check definition: convertTimezone .
I imagine that the common approach is to implement a lookup table, based on the considerations above. In Einstein, I've chosen to write a case statement inside of a computeExpression transformation on my dataflow -- I'll keep my UTC dates/times and produce a separate derived field within my dataset for each local time I create.
Studying the 14 calendars, you'll find that the DAY_IN_YEAR of DST transition dates mostly correlates to the DAY_IN_WEEK of January 1 for a given year. Depending on the DST date rules, leap years that begin on specific (but not all) weekdays are the exception.
For example, under the US rules, in years where January 1 is a Sunday, DST transitions occur on the 71st and 309th days of the year; Monday, 70th and 308th; Tuesday, 69th and 307th; Wednesday, 68th and 306th; Thursday, 67th and 305th (74th and 312th in leap years); Friday, 73rd and 311th; Saturday, 72nd and 310th.
Of course, these numbers are specific to the DST rules considered -- 2nd Sunday in March, 1st Sunday in November -- as is the single exception for leap years that begin on Thursday. I work with teams in the EU, so I've done the legwork for the DST date rules there -- last Sunday in March, last Sunday in October. You'll see in the code below that, of course, the numbers are different, but also that there are multiple exceptions -- leap years that begin on Monday or on Thursday.
So, we can identify the transition dates for a given year, under specific DST date rules, based on the DAY_IN_WEEK of January 1. With that in mind, let's use DAY_IN_WEEK of January 1 as the primary expression for our case structure. For readability, I've chosen simple syntax with nested case statements for identifying leap year exceptions and for the actual definition of DST dates/times.
Sticking to the example above (US rules), here's my original solution. Note that I've already derived a date field called 'est' in a prior computeExpression transformation ( toDate('CreatedDate_sec_epoch' - 5*3600) ), again for readability -- but this could have been done in a single node, with some additional case logic to consider.
case day_in_week(toDate('est_Year' + "-01-01", "yyyy-MM-dd"))
when 1 then case
when (day_in_year(toDate('est_sec_epoch')) == 71 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 71 and day_in_year(toDate('est_sec_epoch')) < 309) or (day_in_year(toDate('est_sec_epoch')) == 309 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
when 2 then case
when (day_in_year(toDate('est_sec_epoch')) == 70 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 70 and day_in_year(toDate('est_sec_epoch')) < 308) or (day_in_year(toDate('est_sec_epoch')) == 308 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
when 3 then case
when (day_in_year(toDate('est_sec_epoch')) == 69 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 69 and day_in_year(toDate('est_sec_epoch')) < 307) or (day_in_year(toDate('est_sec_epoch')) == 307 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
when 4 then case
when (day_in_year(toDate('est_sec_epoch')) == 68 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 68 and day_in_year(toDate('est_sec_epoch')) < 306) or (day_in_year(toDate('est_sec_epoch')) == 306 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
when 5 then case
when string_to_number('est_Year') % 4 == 0 and not (string_to_number('est_Year') % 100 == 0 and string_to_number('est_Year') % 400 != 0) then case
when (day_in_year(toDate('est_sec_epoch')) == 74 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 74 and day_in_year(toDate('est_sec_epoch')) < 312) or (day_in_year(toDate('est_sec_epoch')) == 312 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
else case
when (day_in_year(toDate('est_sec_epoch')) == 67 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 67 and day_in_year(toDate('est_sec_epoch')) < 305) or (day_in_year(toDate('est_sec_epoch')) == 305 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
end
when 6 then case
when (day_in_year(toDate('est_sec_epoch')) == 73 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 73 and day_in_year(toDate('est_sec_epoch')) < 311) or (day_in_year(toDate('est_sec_epoch')) == 311 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
when 7 then case
when (day_in_year(toDate('est_sec_epoch')) == 72 and string_to_number('est_Hour') >= 2) or (day_in_year(toDate('est_sec_epoch')) > 72 and day_in_year(toDate('est_sec_epoch')) < 310) or (day_in_year(toDate('est_sec_epoch')) == 310 and string_to_number('est_Hour') < 1) then toDate('est_sec_epoch' + 3600)
else toDate('est_sec_epoch')
end
end
And here's the adjustment I made for local time in Hungary (CET). I'll point out that I swap in the original UTC time for the hour comparison, since the EU transitions all together at UTC 1:00am (contrary to the rolling transitions in the US, where clocks are changed at 2:00am local time in each time zone).
case day_in_week(toDate('gmt1_Year' + "-01-01", "yyyy-MM-dd"))
when 1 then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 85 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 85 and day_in_year(toDate('gmt1_sec_epoch')) < 302) or (day_in_year(toDate('gmt1_sec_epoch')) == 302 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
when 2 then case
when string_to_number('gmt1_Year') % 4 == 0 and not (string_to_number('gmt1_Year') % 100 == 0 and string_to_number('gmt1_Year') % 400 != 0) then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 91 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 91 and day_in_year(toDate('gmt1_sec_epoch')) < 301) or (day_in_year(toDate('gmt1_sec_epoch')) == 301 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
else case
when (day_in_year(toDate('gmt1_sec_epoch')) == 84 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 84 and day_in_year(toDate('gmt1_sec_epoch')) < 301) or (day_in_year(toDate('gmt1_sec_epoch')) == 301 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
end
when 3 then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 90 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 90 and day_in_year(toDate('gmt1_sec_epoch')) < 300) or (day_in_year(toDate('gmt1_sec_epoch')) == 300 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
when 4 then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 89 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 89 and day_in_year(toDate('gmt1_sec_epoch')) < 299) or (day_in_year(toDate('gmt1_sec_epoch')) == 299 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
when 5 then case
when 'gmt1_Year' % 4 == 0 and not ('gmt1_Year' % 100 == 0 and 'gmt1_Year' % 400 != 0) then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 88 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 88 and day_in_year(toDate('gmt1_sec_epoch')) < 305) or (day_in_year(toDate('gmt1_sec_epoch')) == 305 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
else case
when (day_in_year(toDate('gmt1_sec_epoch')) == 88 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 88 and day_in_year(toDate('gmt1_sec_epoch')) < 298) or (day_in_year(toDate('gmt1_sec_epoch')) == 298 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
end
when 6 then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 87 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 87 and day_in_year(toDate('gmt1_sec_epoch')) < 304) or (day_in_year(toDate('gmt1_sec_epoch')) == 304 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
when 7 then case
when (day_in_year(toDate('gmt1_sec_epoch')) == 86 and string_to_number('CreatedDate_Hour') >= 1) or (day_in_year(toDate('gmt1_sec_epoch')) > 86 and day_in_year(toDate('gmt1_sec_epoch')) < 303) or (day_in_year(toDate('gmt1_sec_epoch')) == 303 and string_to_number('CreatedDate_Hour') < 1) then toDate('gmt1_sec_epoch' + 3600)
else toDate('gmt1_sec_epoch')
end
end
Hope this is clear enough to follow -- hope it helps!
