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?

[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]

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.

[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]

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

[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]

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.