SOLVED

Re: Velocity Script Incorrect Timezone conversion for America/Phoenix

Go to solution
Crystal_Pacheco
Level 4

Velocity Script Incorrect Timezone conversion for America/Phoenix

I am having an issue trying to calculate the correct time for Arizona (America/Phoenix) in velocity script. 

My script looks like this:

 

 

#* Get Lead Time Zone and convert Timezone calculator to lead's timezone*#
#set ($setTimeZone = $TriggerObject.leadTimeZone)
#if (!$setTimeZone || !($setTimeZone == "ET" || $setTimeZone == "CT" || $setTimeZone == "MT" || $setTimeZone == "PT" || $setTimeZone == "AZ"))
    #set($setTimeZone = "ET")
#end

#if ( $setTimeZone == "ET")
    #set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#elseif ( $setTimeZone == "CT")
    #set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Chicago") )
#elseif ( $setTimeZone == "MT")
    #set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Denver") )
#elseif ( $setTimeZone == "PT")
    #set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Los_Angeles") )
#elseif ( $setTimeZone == "AZ")
    #set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/Phoenix") )
#else
    #set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#end

#* Convert the time to Central and then back to lead time zone *#
#set( $inTimeZone = $date.getTimeZone().getTimeZone("America/Chicago"))
#set( $outTimeZone = $defaultTimeZone)
#set( $locale = $date.getLocale() )

#if($TriggerObject.visitDateStart)
    #set( $myTimeStart = $convert.parseDate($TriggerObject.visitDateStart,'MM-dd-yy HH:mm',$locale,$inTimeZone) )
    $date.format('hh:mm a',$myTimeStart,$locale,$outTimeZone)##
#end

 

 

 

Link to the zulu time and its conversions 
Database input time: 2024-01-09T13:30:00Z
Marketo converts to: 2024-01-09 08:30:00

In the output, if the lead timezone is set as "AZ" the result I'm getting in the email is an hour behind.
5:30AM instead of 6:30AM.

I get the time correctly displaying for the other timezones (ET,PT,MT,CT). 
I also tried outputting the timezone being used for America/Phoenix and I receive MST which is correct. 

I am stumped, is there anything I can adjust for AZ to output the correct time?
Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

Re: Velocity Script Incorrect Timezone conversion for America/Phoenix

First, your code is unnecessarily complex. You can do that translation between non-standard/friendly names and real names much more concisely:

 

#* convert two-letter timezone to real IANA timezone*#
#set( $ianaTimeZoneMap = {
  "ET" : "America/New_York",
  "CT" : "America/Chicago",
  "MT" : "America/Denver",
  "PT" : "America/Los_Angeles",
  "AZ" : "America/Phoenix"
 } )
#set( $leadTimeZoneFriendly = $TriggerObject.leadTimeZone )
#set( $leadTimeZoneIANA = $display.alt( $ianaTimeZoneMap.get($leadTimeZoneFriendly), $ianaTimeZoneMap.get("ET")) )

#* parse the time in Central and format in lead time zone *#
#set( $inTimeZone = $date.getTimeZone().getTimeZone("America/Chicago"))
#set( $outTimeZone = $date.getTimeZone().getTimeZone($leadTimeZoneIANA) )
#set( $locale = $date.getLocale() )

#if($TriggerObject.visitDateStart)
    #set( $myTimeStart = $convert.parseDate($TriggerObject.visitDateStart,'MM-dd-yy HH:mm',$locale,$inTimeZone) )
    $date.format('hh:mm a',$myTimeStart,$locale,$outTimeZone)##
#end

 

 

I’m not able to reproduce the problem with AZ. When I set the $TriggerObject.leadTimeZone to AZ and the $TriggerObject.visitDateStart to 12-22-23 14:00 I get this correct output:

 

12:00 PM

 

 

But I’m also not clear on why you believe your custom field visitDateStart is in Chicago time, unless that’s deliberate. True Marketo Date and DateTime fields are in Chicago time, yes. But that format MM-dd-yy HH:mm isn’t Marketo format, ergo it has to be a generic string field with a custom date-like value in it.

View solution in original post

2 REPLIES 2
SanfordWhiteman
Level 10 - Community Moderator

Re: Velocity Script Incorrect Timezone conversion for America/Phoenix

First, your code is unnecessarily complex. You can do that translation between non-standard/friendly names and real names much more concisely:

 

#* convert two-letter timezone to real IANA timezone*#
#set( $ianaTimeZoneMap = {
  "ET" : "America/New_York",
  "CT" : "America/Chicago",
  "MT" : "America/Denver",
  "PT" : "America/Los_Angeles",
  "AZ" : "America/Phoenix"
 } )
#set( $leadTimeZoneFriendly = $TriggerObject.leadTimeZone )
#set( $leadTimeZoneIANA = $display.alt( $ianaTimeZoneMap.get($leadTimeZoneFriendly), $ianaTimeZoneMap.get("ET")) )

#* parse the time in Central and format in lead time zone *#
#set( $inTimeZone = $date.getTimeZone().getTimeZone("America/Chicago"))
#set( $outTimeZone = $date.getTimeZone().getTimeZone($leadTimeZoneIANA) )
#set( $locale = $date.getLocale() )

#if($TriggerObject.visitDateStart)
    #set( $myTimeStart = $convert.parseDate($TriggerObject.visitDateStart,'MM-dd-yy HH:mm',$locale,$inTimeZone) )
    $date.format('hh:mm a',$myTimeStart,$locale,$outTimeZone)##
#end

 

 

I’m not able to reproduce the problem with AZ. When I set the $TriggerObject.leadTimeZone to AZ and the $TriggerObject.visitDateStart to 12-22-23 14:00 I get this correct output:

 

12:00 PM

 

 

But I’m also not clear on why you believe your custom field visitDateStart is in Chicago time, unless that’s deliberate. True Marketo Date and DateTime fields are in Chicago time, yes. But that format MM-dd-yy HH:mm isn’t Marketo format, ergo it has to be a generic string field with a custom date-like value in it.

Crystal_Pacheco
Level 4

Re: Velocity Script Incorrect Timezone conversion for America/Phoenix

By Joe you've got it! 
In your response you mentioned the format:


@SanfordWhiteman wrote:

But that format MM-dd-yy HH:mm isn’t Marketo format, ergo it has to be a generic string field with a custom date-like value in it.


When I changed the format to the Marketo format AKA 

 

yyyy-MM-dd HH:mm:ss

 

It solved the America/Phoenix output to be the correct time, 6:30AM.

Full script here:

 

#* convert two-letter timezone to real IANA timezone*#
#set( $ianaTimeZoneMap = {
  "ET" : "America/New_York",
  "CT" : "America/Chicago",
  "MT" : "America/Denver",
  "PT" : "America/Los_Angeles",
  "AZ" : "America/Phoenix"
 } )
#set( $leadTimeZoneFriendly = $TriggerObject.leadTimeZone )
#set( $leadTimeZoneIANA = $display.alt( $ianaTimeZoneMap.get($leadTimeZoneFriendly), $ianaTimeZoneMap.get("ET")) )

#* parse the time in Central and format in lead time zone *#
#set( $inTimeZone = $date.getTimeZone().getTimeZone("America/Chicago"))
#set( $outTimeZone = $date.getTimeZone().getTimeZone($leadTimeZoneIANA) )
#set( $locale = $date.getLocale() )

#if($TriggerObject.visitDateStart)
    #set( $myTimeStart = $convert.parseDate($TriggerObject.visitDateStart,'yyyy-MM-dd HH:mm:ss',$locale,$inTimeZone) )
    $date.format('hh:mm a',$myTimeStart,$locale,$outTimeZone)##
#end

 


Thanks for your help!