Skip navigation

Do It

2 Posts authored by: Chris Seepe
Chris Seepe

Time Zones Across Eloqua

Posted by Chris Seepe Sep 26, 2014

Update 2014-04-14: Contact List Upload Time Zone Details Updated

Update 2017-12-06: API Endpoint Details Updated

 

As most of you know, the Eloqua servers are set to the Eastern (North America) time zone. That means, all dates/times are stored in the database adjusted to GMT-5 (and most likely GMT-4 when Daylight Saving Time is active). I still don't have an exact, technical description of what happens when the time zone switches from GMT-5 to GMT-4, and back; the system just compensates somehow.

 

Anyway, the times in different parts of the application are adjusted to, or displayed in, different time zones. The problem is, there's little-to-no indication of what time zone is being used for each of the date/time displays. I'm trying to compile a list of the different parts of the application, and what time zone is being used. HINT: Most of it is Eastern Time (GMT-5 at least; not sure entirely about DST/GMT-4).

 

The following is my understanding of the current state of the system, and pulled from various Topliners posts (e.g. What should Eloqua focus on for the next release?, Does Time Zone setting work for a separate user or a whole data center?, Adjusting form submisson time to match timezones?). The first link above has a good overview of this information; might also be worth the time to vote. This is not complete nor authoritative. If anything is incorrect or needs clarification, let me know. Tested at least most of these in E10; I believe the majority should apply to E9.

 

Component / Module / ThingTime Zone UsedNotes
Contact List UploadGMT-5

DST Not Observed

(example below)

Contact Filters / Segment Filters / Shared Filters DateTime FieldsEastern (North America) TimePossibly between GMT-5 and GMT-4 depending on Daylight Saving Time*
Campaign Canvas Compare Contact Fields on a DateTime Field / Program Builder Date ComparisonEastern (North America) TimePossibly between GMT-5 and GMT-4 depending on Daylight Saving Time*
Contact Details / Campaign Contact Entry and Next Evaluation Time / Contact Filter Results

Eloqua User Time Zone

Configurable under Setup > Profile
Insight Subscription Report Scheduling / Insight DateTime Fields / Insight DateTime Range PromptsEastern (North America) TimePossibly between GMT-5 and GMT-4 depending on Daylight Saving Time*

Cloud Connectors (Last Run Time, Date Calculator, etc.)

This needs to be retested for the new AppCloud Framework apps.

Greenwich Mean TimeSet to GMT/UTC. DST Ignored.
Form Submission Data (View in Eloqua)Eloqua User Time ZoneConfigurable under Setup > Profile
Form Submission Data (Export) / Form Submission NotificationsEastern (North America) TimePossibly GMT-5 / GMT-4 depending on DST*
Program Builder Test Mode (System Date and Time Override Input)Eastern (North America) TimePossibly GMT-5 / GMT-4 depending on DST*
Program Builder Test Mode (Display Entered Date and Time Override)Eloqua User Time ZoneConfigurable under Setup > Profile
Program Builder Entry / Exit / Evaluation Histories, Last UpdateEloqua User Time ZoneConfigurable under Setup > Profile
CRM Integration External Calls - Actual Data Written to the CRM - Date/Time Stamps (Date Modified, Date Created, etc.), Activity Dates/TimesEastern (North America) TimePossibly GMT-5 / GMT-4 depending on DST*
API Integration (Input and Output) / Asset Last Modified Date through APIEastern (North America) TimePossibly GMT-5 / GMT-4 depending on DST*
Asset Last Modified Date (View in Eloqua)Eloqua User Time ZoneConfigurable under Setup > Profile
CRM Integration Auto Synch Scheduling - Default TimeEastern (North America) TimeAlthough the interface says "10pm EST", that is not accurate; it follows DST.

 

*Switching between DST needs to be verified.

 

 

API Endpoint Details

 

 

bulk/2.0/contacts/exports (Specifically, the resulting contact export sync data)

By default, all times are in Eastern Time and the same as shown in the UI (if your profile is set to Eastern Time). This applies to system-level fields (e.g. {{Contact.CreatedAt}}) as well as customer-created fields (e.g. {{Contact.Field(C_Inquiry_Capture_Date1)}}).

Now, you can add a parameter to your export definition -- "areSystemTimestampsInUTC":"true" -- and this will change only the system-level fields to UTC. Customer-created fields will remain as Eastern Time.

 

NOTE: "Eastern Time" in this context varies between UTC-4 and UTC-5 depending on the date value in that particular fields.

For example, a Date Created (system field) value of "2017-04-30 04:58:49.423" without the areSystemTimestampsInUTC parameter will return "2017-04-30T08:58:49.423Z" with "areSystemTimestampsInUTC":"true" (meaning UTC-4, as DST was active in the Eastern Time Zone on 2017-Apr-30). Similarly, a Date Created value of "2017-12-06 22:01:34.680" without areSystemTimestampsInUTC will return "2017-12-07T03:01:34.680Z" (UTC-5 as DST was not active on 2017-Dec-07) with that parameter set to "true".

 

rest/2.0/data/contact/<id>

Here, all dates (system fields and customer-created fields) are exported in UTC, irrespective of user's profile settings (unlike the Bulk export, where even with areSystemTimestampsInUTC=true, only the system fields are exported in UTC). The timestamps are in epoch time/Unix time (number of seconds from 1970-01-01 00:00:00 UTC). For example, a date of "2017-12-06 22:01:34" as shown in the UI (Profile:Eastern Time) will be exported as 1493542729 (2017-12-07 03:01:34 -- UTC-5), while a date of "2017-04-30 04:58:49" will be 1493542729 (2017-04-30 08:58:49 -- UTC-4).

 

bulk/2.0 fields, definition, and sync createdAt and updatedAt timestamps are UTC.

 

Just to stress this again: If you created a new Contact date field, the Bulk API will always export that value in Eastern Time (UTC-4/UTC-5 depending on that value's date), irrespective of the "areSystemTimestampsInUTC" parameter. However, the REST API will always export that value in UTC with no option to export the as-is database value.

 

 

 

Update: Times in list uploads are processed according to GMT-5 (NO DST).

List Upload Example: Your Eloqua user profile is set to GMT+2. You upload a list with a date/time of "10/13/2014", which is interpreted as "2014-Oct-13 00:00:00". Currently, Eastern (North America) is observing DST, so it's really GMT-4, here. When you view the contact, the time will show as 7:00AM (not 6:00AM).

 

Update: It seems like Eloqua User Profile Time Zones do not observe DST whatsoever; whatever GMT±XX is shown in the selection, that's the time zone offset used (at least for Contact date/time field displays). In the above example, if the Eloqua user profile is set to "(GMT-05:00) Eastern Time (US & Canada)", the time will show as 12:00AM (not 1:00AM).

 

Update: The default scheduling option for auto synchs is "Auto-Synch Runs on Selected Days at 10pm EST?", but that is not accurate. EST (Eastern Standard Time) is UTC-5, but the scheduling option actually switches between EST and EDT. So, if you were in Thailand (ICT, UTC+7, no DST), between Nov 2 and Mar 8 the synch will run at 10:00AM local time, but between Mar 9 and Nov 1, it will run at 9:00AM local time.

 

 

For details on the date/time text format for list uploads, see List Upload Date/Time Formats

Eloqua stores dates and times internally in a specific date/time data type in the database. However, when contact lists are uploaded, the dates and times are text, and need to be interpreted by the system.

 

In E10's Contacts > Contacts > Upload or Contacts > Segments > Upload Contacts wizards, there is no selection for date format (unlike other parts, where there's a DD/MM/YYYY vs MM/DD/YYYY selector). There are many formats that the system will accept, but my personal favourite is:

 

       YYYY-MM-DD HH:mm:ss (e.g. 2014-10-15 15:21:14)

Unlike the other two common date formats, alphabetical order is automatically chronological order, and this format is much less ambiguous. I hope many of you will join me in the war against ambiguity.

 

     MM/DD/YYYY hh:mm:ss A/PM (e.g. 10/15/2014 3:15:14 AM)

This format is also acceptable for the purposes of the list upload wizards mentioned above.

 

It's also worth noting that the system can accept thousandths of a second (three decimal places), but it doesn't look like that extra data is stored.

 

    "2014-10-15 15:21:14.254" is acceptable. "2014-10-15 15:21:14.2545" will fail.

 

The following table shows different test text data, and the resulting interpreted/recorded date/time:

Date-Only Formats
Date/Time Text (in CSV)Recorded Date/Time (GMT-5 / 24 hr)Note
10/13/20142014-Oct-13 00:00:00MM/DD/YYYY
10/13/142014-Oct-13 00:00:00MM/DD/YY
01/02/032003-Jan-02 00:00:00MM/DD/YY
2014/10/132014-Oct-13 00:00:00YYYY/MM/DD
2014.10.132014-Oct-13 00:00:00YYYY/MM/DD
2014-10-132014-Oct-13 00:00:00YYYY/MM/DD
14/13/10INVALIDMonth '14' does not exist (MM/DD/YY)
13/10/2014INVALIDMonth '13' does not exist (MM/DD/YYYY)
13/10/14INVALIDMonth '13' does not exist (MM/DD/YY)
2014/13/10INVALIDMonth '13' does not exist (YYYY/MM/DD)
2014-13-10INVALIDMonth '13' does not exist (YYYY/MM/DD)
3015-10-133015-Oct-13 00:00:00Years are saved in DB as four-digits
10/11/30153015-Oct-11 00:00:00Years are saved in DB as four-digits
30105-10-13INVALIDFive-digit years are not supported
10/13/30105INVALIDFive-digit years are not supported
Oct 18INVALIDMonths must be numeric
18 OctINVALIDMonths must be numeric
OctINVALIDMonths must be numeric
13/10INVALIDCannot have only YY/MM
2014/10INVALIDCannot have only YYYY/MM
10/13INVALIDCannot have only MM/YY
10/2014INVALIDCannot have only MM/YYYY
01/02INVALIDCannot have only MM/DD (or DD/MM)
10.11.122012-Oct-11 00:00:00. is used as a date separator (MM/DD/YY)
10:11:121900-Jan-01 10:11:12: is used as a time separator (HH/mm/ss)
Date/Time Formats
Date/Time Text (in CSV)Recorded Date/Time (GMT-5 / 24 hr)Note
10/13/2014 14:30:15.2522014-Oct-13 14:30:15MM/DD/YYYY HH:mm:ss.fff
2014/10/13 2:30:15.5642014-Oct-13 02:30:15YYYY/MM/DD H:mm:ss.fff
2014/10/13 2:30:15.564 PM2014-Oct-13 14:30:15YYYY/MM/DD H:mm:ss.fff xM
2014.10.13 14.30INVALIDTime separator should be : (except for fractions of a second)
2014/10/13 2:30:15.5642 PMINVALIDFractions of a second cannot have more than three digits
10/13/2014 14:30:15.252 AMINVALIDCannot specify AM when HH > 12
10/12/2014 2:00PINVALIDCannot use A or P (AM or PM must be used)
2014/10/13 2:30:15.564 AINVALIDCannot use A or P (AM or PM must be used)
2014/10/13 2:30:15.564 PINVALIDCannot use A or P (AM or PM must be used)
2014-10-13 17.25.14.147INVALIDTime separator should be : (except for fractions of a second)
10-11-12 2-5-14INVALIDTime separator should be : (except for fractions of a second)
10-11-12 2.5.14INVALIDTime separator should be : (except for fractions of a second)
10/13/2014 14 PM2014-Oct-13 14:00:00Minutes/Seconds not required when AM/PM is specified
10/13/2014 14 AMINVALIDCannot specify AM when HH > 12
10/13/2014 2 PM2014-Oct-13 14:00:00Minutes/Seconds not required when AM/PM is specified
10/13/2014 14INVALIDTime requires either Minutes or AM/PM
10/12/2014 2:00PM2014-Oct-12 14:00:00Space is not required between ss and AM/PM
2014/10/12 2:00PM2014-Oct-12 14:00:00Space is not required between ss and AM/PM
10/12/2014-2:00PMINVALIDMust use a space between the date and the time
2014/10 2:00 PMINVALIDCannot have only 2 of 3 date components
10/13 2:00INVALIDCannot have only 2 of 3 date components
13/10 2:00INVALIDCannot have only 2 of 3 date components
2014 2:00 PM2014-Jan-01 14:00:00Year only + time is okay
Time-Only Formats
Date/Time Text (in CSV)Recorded Date/Time (GMT-5 / 24 hr)Note
2:45:10 AM1900-Jan-01 02:45:10Date defaults to 1900-Jan-01 when only time is specified
14:12:251900-Jan-01 14:12:25Date defaults to 1900-Jan-01 when only time is specified
14:001900-Jan-01 14:00:00Date defaults to 1900-Jan-01 when only time is specified
2 PM1900-Jan-01 14:00:00Minutes/Seconds not required when AM/PM is specified
2 PINVALIDCannot use A or P (AM or PM must be used)
14INVALIDTime requires either Minutes or AM/PM
10.11.122012-Oct-11 00:00:00. (period) is used as a date separator (MM/DD/YY)
10:11:121900-Jan-01 10:11:12: (colon) is used as a time separator (HH/mm/ss)
24.15.14INVALIDMM cannot be > 12
24:15:14INVALIDHH cannot be > 23
10 11 12INVALID<space> cannot be used as a separator within a date or time (it must be used between the date and the time)
10 11INVALID<space> cannot be used as a separator within a date or time (it must be used between the date and the time)
10/12/2014-2:00PMINVALIDMust use a space between the date and the time

 

 

For details on the effects of time zones, see Time Zones Across Eloqua

 

 

Attached is a spreadsheet of different date/time tests performed, and how they were interpreted by the system (source data for above table).

Filter Blog

By date: By tag: