In RequestReport, what do StartDate and EndDate actually filter?


#1

According to http://docs.developer.amazonservices.com/en_US/reports/Reports_RequestReport.html the StartDate and EndDate select the range of data for the report. For the GET_AMAZON_FULFILLED_SHIPMENTS_DATA report, what fields precisely are used for that?

The documentation doesn’t specify. Amazon support has given me conflicting nonsense answers. The actual report doesn’t seem to filter on any of the fields. It probably comes closest to “shipment-date”, but occasionally has reports returned that are outside the given date range. None of the fields match consistently, it seems.


#2

At this point I think that the field being used for filtering is “shipment-date”. However, it seems that the API is brokenly doing something bizarre to the StartDate and EndDate parameters. My best guess is that they take the inputs, and then apply some timezone offset incorrectly, adding around 9 hours or something? But then after applying that broken time offset, it seems to then truncate the time entirely, rounding it off to the nearest day, but with strange rounding rules (it seems to round up sometimes, and down sometimes). Then it seems to use the StartDate and EndDate parameters with those 2 bugs applied, and filter on “shipment-date”.

This is my best guess at the moment anyway.


#3

Something to keep in mind is that the dates specified in start/end dates are converted to UTC, and returned dates are always UTC.

Partial shipments may also look out of sequence in some scenarios.


#4

I generally avoid worrying about things like this by making requests whose datetimes overlap, then filtering out any results I had already received via previous requests.

bunga bunga!


#5

I only ever use UTC for anything. Non-UTC is a pit of madness. I suppose it might be worth experimenting whether the StartDate and EndDate parameters even respect the ISO 8601 timezone at all. It’s possible MWS ignores the correct UTC “+00:00” timezone and incorrectly interprets it as some other timezone. Right now I’m waiting on support to respond regarding my new information on this bug.

Previously I was doing the overlapping hack. The scary thing about that hack is that there’s seemingly no guaranteed unique primary key in the report. So how can you be sure what is a duplicate?


#6

I’m sure you’ve noticed by now, but Amazon dates consistently use the Z notation for UTC.
2019-07-01T15:55:05.983Z


#7

I’m pretty sure they do. When I wrote our stuff aeons ago I didn’t even think about timezones, so our times go out in local time and it works fine. It took a while before I finally realized that was probably not a good way to do it, and it was working so whatever.

bunga bunga!

(will this finally post?)


#8

All of the GET_AMAZON_FULFILLED_SHIPMENTS_DATA return time fields end in “+00:00”. I don’t see any “Z” shorthand fields. I have had issues with non-UTC datetimes in other reports, but all the GET_AMAZON_FULFILLED_SHIPMENTS_DATA data is nice clean UTC.

(I’m still waiting to hear back from support again. Hopefully they’ve actually passed the bug report along to a developer at this point.)


#9

I never got back a real answer from support. They merely claimed that the StartDate and EndDate fields can be off by up to 24 hours. That is technological nonsense. Not sure what the real answer is. I may try to investigate further.


#10

Hi @True_Primal I was investigating the same type of question and found this page which helped me:

https://sellercentral.amazon.com/gp/help/200453120?language=en_US&ref=ag_200453120_cont_201074420

So the start and end date must filter based on the shipment-date, but the shipment date isn’t necessarily the same as when the shipment actually took place. It’s the date that the shipment was reported to Amazon and added to the report.

The report contains all completed shipments reported to FBA during the specified time period. This may not include all items that were shipped during that time frame if they have not yet been reported to our system. Those items will be reported in a future time period. This ensures that the report data will always be consistent for any given date range.

Shipment dates are based on when the shipment was reported to the system, which is generally a few hours after the actual ship date. Other reports may calculate shipment dates differently. All dates are in GMT.


#11

That page doesn’t have anything to do with the issue though. It doesn’t matter that “shipment-date” may be off a little bit from the actual second in time the box went on the truck. I think Amazon support was never technically knowledgeable enough to get past that though.


#12

Ok here’s what my investigation showed as far as how these layers of crazy bugs work:

  • MWS takes the StartDate and EndDate, and ignores the timezone information (which in my case is always UTC).
  • MWS then converts StartDate and EndDate to Pacific Time, on top of which they’re using Daylight Savings offsets.
  • After erroneously converting to Pacific Time Daylight Savings adjusted, it then erroneously truncates the hour/time component entirely, turning it into a date time of midnight Pacific Time.
  • Then it takes that midnight Pacific date time, and converts it back to UTC, and filters the “shipment-date”, which are all seemingly in UTC.

So to take a specific example of how this MWS bug functions, say you filter from 2018-02-28T08:00+00:00 to 2018-03-31T08-00+00:00. The StartDate gets converted to erroneous Pacific Time with a -8 daylight offset, so it becomes 2018-02-28T00:00. Because it’s March however, the EndDate gets a -7 daylight offset, so it becomes 2018-03-31T01:00. The UTC StartDate is then 2018-02-28T00:00 but the UTC EndDate gets rounded up to include all of 2018-03-31, so entries are returned up until 2018-03-31T23:59:59.

If the EndDate is 2018-03-31T07-00+00:00, then it becomes erroneously converted to 2018-03-31T00:00, and you lose out on getting every shipment-date in the 2018-03-31 day.


#13

At the end of the day - the only thing that works is do a lot of overlapping date/time range calls, understand the natural keys of objects, and deal with duplicates and change data capture. That is MWS 101…