[SalesForce] Handling Dates in Einstein Analytics UTC vs Local DateTime

Our users are using the New York timezone in Salesforce (UTC – 4 or -5 depending on DST). It appears that the dates are stored in SF as UTC. Salesforce reports that need date ranges work fine because the UI takes the users Timezone into consideration. It appears that Wave (Einstein Analytics) does not do this natively/automagically. Is there a setting for Wave/Einstien to force the DataFlow to generate DataSets with the local date/time? All the info I found online about this indicates that a new DateTime field needs to be created using the todate() function, something like this: "saqlExpression": "toDate(CreatedDate_sec_epoch – 4*3600)"

Best Answer

True, Many of the MNC companies face this Challenge. You found the solution yourself. In Einstein Analytics you can add a computed Date field which can calculate the user time zone. for example you can create a field something like below and use it everywhere

"Extract_Opportunity": {
  "action": "computeExpression",
  "parameters": {
    "source": "Digest_Opportunity",
    "mergeWithSource": true,
    "computedFields": [
     {
      "name": "CreatedDateNew",
      "type": "Date",
      "format": "MM/dd/yyyy",
      "saqlExpression": "toDate(CreatedDate_sec_epoch - 8*3600)"
     }
    ]
  }
},

Related Solutions

[SalesForce] Date input taking into account users time zone offset

Date is timezone non-aware, in fact, so is datetime (its stored at the server as GMT and re-rendered based on users TZ).
Unless you hard-code a base time for your date in Apex code/Trigger, your going to want store a datetime value instead of a date. If you dont want the time part to show up, look into creating a formula field that renders the date based upon the (hidden) datetime field. That should maintain the correct date rendered for their timezone.

[SalesForce] ExactTarget Date and Time guidelines in AMPscript

The documentation on the data extension data types reads:

Date

A system date

You can use an AMPscript function to format this value when you present it in a message

Attributes of a date type will accept dates in various formats, such as 1/1/2005 or January 1, 2005

All dates are stored in the format MM/DD/YYYY, and if a date is entered in a different format, the system will convert it to the proper format when storing it

Valid date values begin after 12/31/1772 and before 01/01/10000

You can add a HH:MM time value following the date value, such as 12/12/2012 12:12 PM.

As long as your data extension uses the data format for the column, and your string is in the proper format according to the above guidelines, you should be ok.

It appears that we do not, however, store as a 24-hour time (which btw is not GMT per se, unless you are not using a timezone offset). A simple fix for this would be to check for AM/PM and add 12 when parsing from the DE, and check for >12 and modify when going back to the DE to store.

To use Concat() to pull individual items together, the string would have to match the above valid date format.

CONCAT(@month,"/",@day,"/",@year," ",@hour,":",@minute," ",@ampm)

This will produce a valid string, especially when using DatePart(), since it returns a string of the segment you requested.

Best Answer

Related Solutions

[SalesForce] Date input taking into account users time zone offset

[SalesForce] ExactTarget Date and Time guidelines in AMPscript

Related Topic