Converting timestamp from SFMC Mobile Push Data Extract to Correct UTC in AEP | Community
Skip to main content
RyanMoravick
RyanMoravick
Level 4
September 27, 2024
Solved

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

  • September 27, 2024
  • 1 reply
  • 1120 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Parvesh_Parmar

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

1 reply

Parvesh_Parmar
Community Advisor
Parvesh_ParmarCommunity Advisor
Community Advisor
September 28, 2024

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.
  1. 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

Parvesh Parmar – Adobe Community Advisor https://www.linkedin.com/in/parvesh-parmar/
    RyanMoravick
    RyanMoravickAuthor
    Level 4
    September 29, 2024

    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
      Community Advisor
      Parvesh_ParmarCommunity AdvisorAccepted solution
      Community Advisor
      September 29, 2024

      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

      Parvesh Parmar – Adobe Community Advisor https://www.linkedin.com/in/parvesh-parmar/
        Terms & ConditionsAccessibility statement

        Loading footer...