Timezone of timestamps on custom fields of opportunity object (Velocity) | Community
Skip to main content
A
Anonymous
October 11, 2018
Question

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

  • October 11, 2018
  • 2 replies
  • 4335 views

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?

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

2 replies

SanfordWhiteman
SanfordWhiteman
Level 10
October 11, 2018

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

A
Anonymous
November 14, 2018

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
SanfordWhiteman
Level 10
November 14, 2018
...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.

    Jay_Jiang
    Jay_Jiang
    Level 10
    November 14, 2018

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

    A
    Anonymous
    November 14, 2018

    Jay,

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

    Jay_Jiang
    Jay_Jiang
    Level 10
    November 14, 2018

    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
      Terms & ConditionsAccessibility statement

      Loading footer...