Re: Timezone for Bulk export API of activity

Saptarshi_Konar · ‎11-06-2019

I am confused regarding the timezone, when a bulk export API for activity is called.

Example-

API- /bulk/v1/activities/export/create.json

JSON - {
     "format": "CSV",
   "filter": {
            "createdAt": {
                  "startAt": "2018-10-16T00:00:00Z",
                  "endAt": "2018-10-16T23:59:59Z"
               }
         }
}

I have tested with Date Range

"startAt": "2018-10-15T00:00:00Z",
"endAt": "2018-10-16T23:59:59Z"
"startAt": "2018-10-17T00:00:00Z",
"endAt": "2018-10-17T23:59:59Z"

API - /bulk/v1/activities/export/{exportId}/file.json

Result- No data found for all this time range

But when the input is -

"startAt": "2018-10-16T00:00:00Z",
"endAt": "2018-10-17T23:59:59Z"

Result- 13 records found

marketoGUID	leadId	activityDate
1913	1234	2018-10-16T16:26:06Z
1916	1234	2018-10-16T16:26:13Z
1921	1234	2018-10-16T16:31:22Z
1923	1234	2018-10-16T16:31:52Z
1929	134	2018-10-16T16:32:03Z
1947	118	2018-10-16T17:36:00Z
1950	113	2018-10-16T17:36:10Z
1969	113	2018-10-16T18:34:35Z
1972	213	2018-10-16T18:34:41Z
2003	987	2018-10-16T21:07:29Z
2006	987	2018-10-16T21:07:41Z
2012	1171	2018-10-16T21:12:10Z
2014	1171	2018-10-16T21:12:22Z

So which time zone is considered at the time of request and what is the timezone of the activityDate in the CSV output?

SanfordWhiteman · ‎11-06-2019

Datetimes are stored in UTC (that's the internal definition of the type, it's a +/- offset from epoch UTC as it would be in every language I know of). You don't store, or rather can't store, datetimes in a database in local time because the time will be interpreted in UTC anyway. (You can try to unmangle it on the way out, but it's hopeless proposition.)

In your case I don't see that this pertains to timezones, anyway, since you're using the same timezone all around.

It seems you're saying date range A does not return results, but date range B which is a subset of date range A does return results. This is definitely unexpected, again regardless of timezone. You absolutely positive you applied the same activity filters to the same set and to the subset?

Saptarshi_Konar · ‎11-07-2019

Sanford Whiteman Actually it is not a subset.

I want the data as per EST( considering the Daylight Saving ).

So I tried the following request-

API- /bulk/v1/activities/export/create.json

JSON - {
     "format": "CSV",
   "filter": {
            "createdAt": {
                  "startAt": "2018-10-16T00:00:00Z America/New_York",
                  "endAt": "2018-10-16T23:59:59Z America/New_York"
               }
         }
}

But in the output CSV from the api "/bulk/v1/activities/export/{requestId}/file.json", I am not getting any entries for the timeline.

But for the

"startAt": "2018-10-16T00:00:00Z",
"endAt": "2018-10-17T23:59:59Z"

or

"startAt": "2018-10-16T00:00:00Z America/New_York",
"endAt": "2018-10-17T23:59:59Z America/New_York"

which is a 2 days interval I am getting below entries which all are dated between "2018-10-16T00:00:00"

to 2018-10-16T22:00:00

marketoGUID	leadId	activityDate
2913	169	2018-10-16T16:26:06Z
2916	169	2018-10-16T16:26:13Z
2921	169	2018-10-16T16:31:22Z
2923	169	2018-10-16T16:31:52Z
2929	169	2018-10-16T16:32:03Z
2947	13	2018-10-16T17:36:00Z
2950	13	2018-10-16T17:36:10Z
2969	13	2018-10-16T18:34:35Z
2972	13	2018-10-16T18:34:41Z
3003	87	2018-10-16T21:07:29Z
3006	87	2018-10-16T21:07:41Z
3012	171	2018-10-16T21:12:10Z
3014	171	2018-10-16T21:12:22Z

Is the activityDate in CSV always show the date time in UTC?
what should be the Ideal input for the request if I want to fetch data in EST timezone for the above example?
Is marketo instance timezone is related to the API timezone?

SanfordWhiteman · ‎11-07-2019

Your timezone formats are invalid. A timestamp can't be in UTC and in America/New_York simultaneously, no such format exists in ISO 8601 (nor does the concept make sense in reality).

Request the time ranges in UTC (Z) only. If you need to do a translation from a given tz, do that in your client code. It doesn't go into the payload.