Timezone for Bulk export API of activity

Highlighted

Timezone for Bulk export API of activity

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?

3 REPLIES 3
Highlighted
Level 10 - Community Moderator

Re: Timezone for Bulk export API of activity

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?

Highlighted

Re: Timezone for Bulk export API of activity

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?
Highlighted
Level 10 - Community Moderator

Re: Timezone for Bulk export API of activity

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.