Converting timestamp from SFMC Mobile Push Data Extract to Correct UTC in AEP

[RyanMoravick]

Hey there, 

Im noticing that my timestamp for a mobile push opened event in AEP is an hour ahead. (Should be 11:25 am but is showing 12:25pm). In SFMC, I am performing a mobile push data extract to extract the previous days mobile push engagement to send to AEP. The timezone selected for the data extract from SFMC is GMT. Is there a way to keep the extract in EST and convert the correct UTC timestamp in the AEP mapping step using calculated fields?

[Parvesh_Parmar]

Hello @RyanMoravick ,

If your data is exported in EST and needs to be converted to UTC before ingestion into AEP, you can use the zone_date_to_utc function during data ingestion.

1. Use zone_date_to_utc to convert EST to UTC:
• Example: If your timestamp is 2022-12-01T11:25:00 EST, you would use:

zone_date_to_utc(2022-12-01T11:25:00 EST)

• This converts the timestamp to 2022-12-01T16:25:00Z (UTC). Since EST is 5 hours behind UTC, you add 5 hours.

2. Handle Daylight Saving Time:

• If your data might be in either EST or EDT, use 'America/New_York' in the conversion. This will automatically adjust for Daylight Saving Time:

                       zone_date_to_utc(2022-06-01T11:25:00 America/New_York)

This way, your timestamps will be in UTC, ensuring consistency in AEP.

Please find the documentation of available function below.

https://experienceleague.adobe.com/en/docs/experience-platform/data-prep/functions

Kr,

Parvesh

[RyanMoravick]

Hi @Parvesh_Parmar thank you for this! Im having an issue getting the correct syntax as I am having to convert the timestamp from a string to date time using the following function:

date(DateTimeSend,"M/d/yyyy h:mm:ss a",now())

This is the new function I am attempting:

zone_date_to_utc(date(DateTimeSend, "M/d/yyyy h:mm:ss a", now()), 'America/New_York')

How would I incorporate the zone_date_to_utc function with the above function? 

Thank you

[Parvesh_Parmar · Accepted solution]

Hi @RyanMoravick ,

Regarding the DateTime format you've mentioned, "M/d/yyyy h:mm:ss a", I believe this is coming from SFMC, and I noticed that there's no time zone information included in the format. Generally, it's important to have the time zone information for accurate date-time conversions, and ideally, it should be like "M/d/yyyy h:mm:ss a z".

Since there's no time zone information in your data, the conversion becomes a bit more complex. Here are a couple of suggestions:

Option 1: Manually Set the Time Zone (e.g., EST)

You can assume that your date is in a specific time zone, for example, EST (Eastern Time). You can use the following code to set the time zone and convert to UTC:

format(zone_date_to_utc(zone_date_to_zone(date(DateTimeSend, "M/d/yyyy h:mm:ss a"), "America/New_York")), "yyyy-MM-dd'T'HH:mm:ss'Z'")

In this example:

• "DateTimeSend" represents your input value, such as "1/2/2024 3:45:09 PM".
• "America/New_York" is used to specify the Eastern Time Zone (EST/EDT).

Option 2: Append Time Zone Information Directly to the String

If the above solution doesn't work, you can hard-code the time zone information by appending it to your DateTimeSend value directly, like so:

format(zone_date_to_utc(date(concat(DateTimeSend + " PST"), "M/d/yyyy h:mm:ss a z")), "yyyy-MM-dd'T'HH:mm:ss'Z'")

In this case:

• The time zone "PST" is appended to the original DateTimeSend value.
• "M/d/yyyy h:mm:ss a z" is used as the format, where z represents the time zone.

Please give these approaches a try and let me know if you have any questions.

Best,
Parvesh

[RyanMoravick]

Thank you so much @Parvesh_Parmar option 1 seems to work for this use case.