Timezone of timestamps on custom fields of opportunity object (Velocity)

[Anonymous]

I am using some velocity scripts to format the time display of timestamp  fields on an Opportunity object.

I am not sure how to determine what timezone the timestamp field comes in as, or even if it is consistent for every lead.

Here is an example:

There is a custom SFDC field on the opportunity named appointment.

In the SFDC UI, a sales rep manually enters the time  for an appointment in Denver (Mountain Time).  It looks like  "2018-10-10 3:00PM".

In Marketo, when I pull that appointment field out of the opportunity object, and display it raw it looks like this:   "2018-10-10 17:00"

That is 2 hours offset from the time in the UI.  (15:00 is 3pm, 17:00 is 5pm).

So, if I interpret the string representation of the appointment time I get in Velocity as being in Eastern time, convert it to a Date, and the display the data as Mountain time I get back to the desired "2018-10-10 3:00PM"

But my question is how do I  KNOW (with certainty) what time zone the custom field on the opportunity record is stored as?  I initially though it would be in UTC time (that would be nice), or in Pacific Time (which is the setting for their Marketo Admin Location).

If this were a custom object, we would know since we control the population of the data into custom objects.  But for Opportunity it is controlled either by SFDC or Marketo or both. But where would I look  in SFDC/Marketo configuration  to see the timezone for opportunity timestamp fields?

[Jay_Jiang]

Marketo servers are Central Time not Eastern Time that you've discerned.

[Anonymous]

Jay,

Are you suggesting that all timestamp from Marketo opportunity timestamp fields will ALWAYS be in Central timezone?

[Jay_Jiang]

According to posts here, Marketo server time will always be -5 UTC. Format for Importing a DateTime field value from CSV

EDIT: I just dug up a piece of velocity code I've used previously based off Sanford's post. It's converting Central Time to Australian Time and I've had no issues.

#set ($fn = ${lead.Lead_Owner_First_Name})
#set ($ln = ${lead.Lead_Owner_Last_Name})
#set ($em = ${lead.Lead_Owner_Email_Address})
#set ($apptloc = ${OpportunityList.get(0).sageOppoAppointmentLocation})
#set ($firstappt = ${OpportunityList.get(0).sageOppoAppointmentDate})
#set ($inTimeZone = $date.getTimeZone().getTimeZone('US/Central') )  
#set ($outTimeZone = $date.getTimeZone().getTimeZone('Australia/Sydney') )  
#set ($locale = $date.getLocale() )  
#set ($firstapptstart = $convert.parseDate($firstappt,'yyyy-MM-dd H:mm:ss',$locale,$inTimeZone) )  
#set ($firstapptstartd = $date.format('yyyy-MM-dd',$firstapptstart,$locale,$outTimeZone))
#set ($firstapptstartt = $date.format('H:mm:ss',$firstapptstart,$locale,$outTimeZone))
#set ($calca = $math.toNumber($firstapptstart))
#set ($calcb = $math.add($calca,5400000))
#set ($firstapptend = $date.toDate($calcb))
#set ($firstapptendd = $date.format('yyyy-MM-dd',$firstapptend,$locale,$outTimeZone))
#set ($firstapptendt = $date.format('H:mm:ss',$firstapptend,$locale,$outTimeZone))
ics.agical.io/?description=Hi%20${lead.FirstName},%5C%6E%5C%6ELooking%20forward%20to%20meeting%20you.%5C%6E%5C%6ERegards,%5C%6E${fn}%20${ln}&organizer=${em}&location=${apptloc}&dtstart=${firstapptstartd}T${firstapptstartt}Z&dtend=${firstapptendd}T${firstapptendt}Z&subject=Meeting%20with%20${fn}%20${ln}&reminder=1440

[SanfordWhiteman]

According to posts here, Marketo server time will always be -5 UTC

That was somebody's shorthand for Central, they shouldn't have implied a fixed offset.

[SanfordWhiteman]

You can trust the tz will be the same for all leads and over time. For SFDC-originated Oppties, it's an SFDC setting.

[Anonymous]

Thanks Sanford.

This was all working find until the end of daylight savings, and now the offset has changed by an hour.

I don't see anyway to get the timezone/offset information from an Opportunity timestamp field. 
The raw timestamp I get back within Velocity just comes back as a string that looks like:  "2018-10-10 17:00:00"

I really need to get the timezone information from that.  Is there any way to get that?

[SanfordWhiteman]

...and now the offset has changed by an hour.

Indeed, you shouldn't be dealing with literal offsets, because of daylight savings.

You should use the DST-aware IANA name of the timezone, "America/Chicago". I've done a couple of blog posts about this.

[Anonymous]

Yes, but that is exactly what I am using, the timezone names for both interpreting the incoming date string and converting to an output string.

#set( $SiteTimeZone = 'America/Denver' )

##set( $SiteTimeZone = 'America/Chicago' )

#set( $DataTimeZone = 'America/New_York' )

I should point out that the timestamp fields are SFDC custom fields on the opportunity object. They are not system timestamps like created/updated.

Sales reps are entering in the times through the SFDC UI, and different sales reps may have different locale settings.

I wish there was a way that the velocity timestamp string carried through the timezone information for the incoming timestamp so things would be explicit.

[SanfordWhiteman]

I wish there was a way that the velocity timestamp string carried through the timezone information for the incoming timestamp so things would be explicit.

Well, that's not really the problem. In SFDC the value is stored and exported as UTC (as it should be in any database). And in turn somewhere in Marketo it's also stored in UTC. But by the time you see it in Velocity, it's been serialized (stringified) in the pod timezone, which is Central.

This is clunky, but as long as you know it's happening should be predictable.

What you seem to be saying is that the serialization is not itself adjusting for daylight savings. But if it just broke at the daylight savings shift a few days ago, that means it would be stored in CDT, not CST (since right now America/Chicago is the same as CST).  Can you parse it as "CDT" and see what happens?

[SanfordWhiteman]

Are you sure the DST shift table is up-to-date, though? Unfortunately I'm almost certain it's not given the age of the JVM. In other words, the shift happens, but doesn't happen on the right day of the year.

[SanfordWhiteman]

In other words, the shift happens, but doesn't happen on the right day of the year.

OK, good news. The tzdb version looks just fine for keeping up with the Central time shift. So it's not that.

[Anonymous]

Interesting thought!  But I don't even know where to begin to check that...maybe I need to open a ticket with Marketo support?

[SanfordWhiteman]

I haven't been able to reproduce what you've described, Bill. The UTC offset changes with the DST shift in my tests.

That is, November 1, 2018 at 10:51pm, entered from instance timezone EDT, is serialized as 2018-11-01 21:51:00 as expected, and November 15, 2018 at 10:51 pm (on the other side of the DST shift) is serialized as 2018-11-01 21:51:00, also as expected. Each one of these is -01:00 from Eastern, i.e. the equivalent in Central. So if parsed as America/Chicago they are the right point in time.

But it's possible that I'm not capturing the same conditions that you have. As it's very important to me, as keeper of the only Marketo-centric Velocity content out there, to cover all exceptional cases, please DM me so we can research this together and I can document the special case if you've found one.

[SanfordWhiteman]

Let me look into it. I doubt support will be any help in this area.