2

I'm in the UK and having issues importing dates into Einstein due to them being in UTC format while we're currently in UTC+1 due to Daylight Savings Time. I know I need to add some compute nodes to my dataflows to deal with this, and I'm clear on how to add an hour to a timestamp.

What I'm not clear on is how I can tell Einstein which dates need to have an hour added to them, otherwise when DST ends all times will again be out by an hour.

What I need is a SAQL expression that will determine if a date occurs between the last Sunday of March, and the Last Sunday of October. Is anybody able to help with this? If this can't be done in this way I'm open to suggestions for other ways around this issue

  • This is a great thread. I'm working an an approach that doesn't require coding for a fixed set of years. I imagine by it's age, you've likely found your answer already. In the future, consider posting to the EA trailblazer group as it gets a lot of traffic. success.salesforce.com/_ui/core/chatter/groups/… – Pete Lyons Nov 26 '18 at 23:11
1

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!

| improve this answer | |
  • Forgot to mention the logic for identifying leap years. If what I did in the code with the modulo operations doesn't make sense, read this. – xkb Sep 7 '18 at 22:21
1

I tried xkb's suggestion, but was unable to get it working, receiving a non-descript error message each time.

I eventually resorted to hardcoding the date ranges where DST should apply into a case statement. While inelegant, this is the recommendation of Salesforce specialists in lieu of any proper timezone handling (which is coming in the near future)

I initially tried doing it all in a single case statement, but it was a pain to debug (working with dates is painful in EA) so I ended up with a 3 step approach as suggested here

Field Name: StatusEndDate_UTC

toDate(substr(StatusEndDate,1,24),"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")

Field Name: StatusEndDate_epoch

date_to_epoch(StatusEndDate_UTC)

Field Name: StatusEndDate_GMT

case 
    when StatusEndDate_epoch >= 1427590800 and StatusEndDate_epoch < 1445738400 then toDate((date_to_epoch(StatusEndDate_UTC) + 3600)) 
    when StatusEndDate_epoch >= 1459040400 and StatusEndDate_epoch < 1477792800 then toDate((date_to_epoch(StatusEndDate_UTC) + 3600)) 
    when StatusEndDate_epoch >= 1490490000 and StatusEndDate_epoch < 1509242400 then toDate((date_to_epoch(StatusEndDate_UTC) + 3600)) 
    when StatusEndDate_epoch >= 1521939600 and StatusEndDate_epoch < 1540692000 then toDate((date_to_epoch(StatusEndDate_UTC) + 3600)) 
    when StatusEndDate_epoch >= 1553994000 and StatusEndDate_epoch < 1572141600 then toDate((date_to_epoch(StatusEndDate_UTC) + 3600)) 
    when StatusEndDate_epoch >= 1585443600 and StatusEndDate_epoch < 1603591200 then toDate((date_to_epoch(StatusEndDate_UTC) + 3600)) 
    else toDate(date_to_epoch(StatusEndDate_UTC)) 
end

To replicate you'll just need to change the field names along with the initial field that starts the ball rolling in the _UTC field

| improve this answer | |
  • Great solution, I ended up using the same for DST timezones. Why did you use the substr() function here? substr(StatusEndDate,1,24) – Mission Mike Jul 15 at 17:47
0

I did end up making that video. Timezone offset including DST and leap year. https://www.youtube.com/watch?v=IFnr5BDgM3U

The below dataflow JSON is used to offset Created Date to EST. Pardon the formatting, can't figure out how to make it indent right.

{
  "regEST_Opptys": {
    "action": "sfdcRegister",
    "parameters": {
      "name": "EST_Opptys",
      "alias": "EST_Opptys",
      "source": "timeZoneOffset"
    }
  },
  "getOpptys": {
    "action": "sfdcDigest",
    "parameters": {
      "fields": [
        {
          "name": "Name"
        },
        {
          "name": "CreatedDate"
        }
      ],
      "object": "Opportunity"
    }
  },
  "timeZoneOffset": {
    "action": "computeExpression",
    "parameters": {
      "source": "getOpptys",
      "mergeWithSource": true,
      "computedFields": [
        {
          "name": "Historical2ndSunday",
          "saqlExpression": "toDate((date_to_epoch(toDate(CreatedDate_sec_epoch)) - ((day_in_year(toDate(CreatedDate_sec_epoch))*86400)) + (66*86400)) +\n((7 - trunc(((date_to_epoch(toDate(CreatedDate_sec_epoch))/86400)) - day_in_year(toDate(CreatedDate_sec_epoch)) + 66 +3)%7)*86400)) ",
          "format": "yyyy-MM-dd",
          "label": "Historical2ndSunday",
          "type": "Date"
        },
        {
          "name": "HistoricalMar7",
          "saqlExpression": "toDate((date_to_epoch(toDate(CreatedDate_sec_epoch)) - ((day_in_year(toDate(CreatedDate_sec_epoch))*86400)) + (66*86400)))",
          "format": "yyyy-MM-dd",
          "label": "HistoricalMar7",
          "type": "Date"
        },
        {
          "precision": 16,
          "name": "LeapYearOffset",
          "saqlExpression": "1 - ceil((string_to_number(toString(now(),\"yyyy\"))/4) - trunc((string_to_number(toString(now(),\"yyyy\"))/4)))",
          "scale": 2,
          "label": "LeapYearOffset",
          "type": "Numeric"
        },
        {
          "precision": 16,
          "name": "HistoricalLeapOffset",
          "saqlExpression": "1 - ceil((string_to_number(toString(toDate(CreatedDate_sec_epoch),\"yyyy\"))/4) - trunc((string_to_number(toString(toDate(CreatedDate_sec_epoch),\"yyyy\"))/4)))",
          "scale": 2,
          "label": "HistoricalLeapOffset",
          "type": "Numeric"
        },
        {
          "name": "Mar7",
          "saqlExpression": "toDate((date_to_epoch(now()) - ((day_in_year(now())*86400)) + (66*86400)))",
          "format": "yyyy-MM-dd",
          "label": "Mar7",
          "type": "Date"
        },
        {
          "precision": 16,
          "name": "Mar7Epoch",
          "saqlExpression": "(date_to_epoch(now()) - ((day_in_year(now())*86400)) + (66*86400))",
          "scale": 2,
          "label": "Mar7Epoch",
          "type": "Numeric"
        },
        {
          "precision": 16,
          "name": "HistoricalMar7Epoch",
          "saqlExpression": "(date_to_epoch(toDate(CreatedDate_sec_epoch)) - ((day_in_year(toDate(CreatedDate_sec_epoch))*86400)) + (66*86400))",
          "scale": 2,
          "label": "HistoricalMar7Epoch",
          "type": "Numeric"
        },
        {
          "name": "StartDST",
          "saqlExpression": "toDate(Mar7Epoch + (LeapYearOffset * 86400) + ((8 - day_in_week(toDate(Mar7Epoch + (LeapYearOffset*86400)))) * 86400))",
          "format": "yyyy-MM-dd",
          "label": "StartDST",
          "type": "Date"
        },
        {
          "name": "HistoricalStartDST",
          "saqlExpression": "toDate(HistoricalMar7Epoch + (HistoricalLeapOffset * 86400) + ((8 - day_in_week(toDate(HistoricalMar7Epoch + (HistoricalLeapOffset*86400)))) * 86400))",
          "format": "yyyy-MM-dd",
          "label": "HistoricalStartDST",
          "type": "Date"
        },
        {
          "precision": 16,
          "name": "startDSTEpoch",
          "saqlExpression": "Mar7Epoch + (LeapYearOffset * 86400) + ((8 - day_in_week(toDate(Mar7Epoch + (LeapYearOffset*86400)))) * 86400)",
          "scale": 2,
          "label": "startDSTEpoch",
          "type": "Numeric"
        },
        {
          "name": "CreatedDateEST",
          "format": "yyyy-MM-dd HH:mm:ss",
          "saqlExpression": "toDate(case \nwhen date_to_epoch(now()) > (startDSTEpoch + 7200) && \ndate_to_epoch(now()) < ((startDSTEpoch + 7200) + (238*86400) )\nthen CreatedDate_sec_epoch - 18000\nelse CreatedDate_sec_epoch - 14400 end)",
          "label": "CreatedDateEST",
          "type": "Date"
        }
      ]
    }
  }
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.