Workflow

Fixing the BST/UTC Date-Filter Bug in Dropshipping Order Pulls

August 5, 2026 · 7 min read

You set up your Fetch Order Tracking automation to pull new orders from AliExpress daily, using a simple gmt_create date filter. You expect to see all orders from the previous 24-hour period. But then you notice discrepancies: some orders from yesterday are missing, and a few from today have already appeared. This isn't a random glitch; it's a common timezone-related bug, specifically when dealing with British Summer Time (BST) and Coordinated Universal Time (UTC).

The core issue arises because many APIs, including AliExpress, often report timestamps in UTC, while your local system or the spreadsheet's default interpretation might be set to a different timezone, especially one that observes daylight saving time (DST) like BST.

The BST-UTC Discrepancy Explained

During BST, the UK is UTC+1. If your automation runs at 00:00 BST (midnight), and you query for orders created 'yesterday' based on a simple date subtraction, you're actually querying for a period that starts and ends differently in UTC. For example:

00:00 BST on April 2nd is 23:00 UTC on April 1st.
If you filter for gmt_create between 'April 1st 00:00 BST' and 'April 1st 23:59 BST', your API request, if interpreted in UTC, will be for orders between 'March 31st 23:00 UTC' and 'April 1st 22:59 UTC'. This misses an entire hour of orders.

This leads to either pulling too many orders (overlapping into the next day) or, more critically, missing orders (creating gaps) depending on how your date range is constructed and how the API interprets the 'start' and 'end' of the day.

Common Scenarios Leading to Missing or Duplicate Orders

Scenario 1: Simple Date Subtraction (Missing Orders)

You run a script daily at 00:00 BST to pull orders from the previous day. Your script calculates yesterday_start = NOW() - 1 day and yesterday_end = NOW() - 1 day + 23 hours 59 minutes 59 seconds. If NOW() is 00:00 BST (23:00 UTC) on April 2nd, then:

yesterday_start (April 1st 00:00 BST) becomes March 31st 23:00 UTC.
yesterday_end (April 1st 23:59 BST) becomes April 1st 22:59 UTC.

Any AliExpress orders created between April 1st 22:59 UTC and April 1st 23:59 UTC will be missed by this pull. These orders will only be picked up by the next day's pull, or not at all if your filter is too strict.

Scenario 2: Fixed UTC Time (Duplicates or Misses at DST Transitions)

You try to be clever and always query for a fixed UTC window, e.g., gmt_create between YYYY-MM-DD 00:00:00 UTC and YYYY-MM-DD 23:59:59 UTC. This works consistently for the API, but if your local tracking system expects a 'day' to align with your local BST/GMT day, then you'll have 23-hour or 25-hour 'days' around DST transitions, leading to misalignments in your local reporting.

The critical insight is that a 'day' is not a universally fixed 24-hour period when different timezones and daylight saving rules are involved. Always define your API date ranges in the timezone the API expects (usually UTC) and then convert for local display or reporting.

The Robust Solution: Timezone-Aware Filtering

The most reliable way to handle this is to always convert your desired local time range into UTC for the API call. Here's a step-by-step approach for a daily pull:

1. Define Your Local Reporting Day

Decide what 'yesterday' means to *your* business. For most dropshippers, it means the period from 00:00:00 to 23:59:59 in your local timezone (e.g., BST) for the previous calendar day.

2. Determine the UTC Start and End Times for Your Local Day

If you want to pull orders created during April 1st, 2024 (00:00:00 BST to 23:59:59 BST):

Start time (local): 2024-04-01 00:00:00 BST
End time (local): 2024-04-01 23:59:59 BST

Now, convert these to UTC:

Start time (UTC): 2024-04-01 00:00:00 BST is 2024-03-31 23:00:00 UTC
End time (UTC): 2024-04-01 23:59:59 BST is 2024-04-01 22:59:59 UTC

This is the exact range you should send to the AliExpress API for the gmt_create_start and gmt_create_end parameters.

3. Implement a Rolling Lookback Window (Recommended)

Instead of relying on a strict 24-hour window, use a slightly overlapping lookback window and then de-duplicate in your Google Sheet. This provides fault tolerance against minor timing discrepancies or network delays.

For example, if you run your pull at 00:15 BST daily:

Calculate current_time_utc = NOW() (in UTC).
Set gmt_create_end = current_time_utc.
Set gmt_create_start = current_time_utc - 48 hours (or 72 hours if you want more buffer).
Send this broad UTC range to the API.
In your Google Sheet, use a unique identifier (e.g., order_id) and a UNIQUE() function or a script to remove duplicates. This ensures you capture everything without missing orders, even if a previous pull failed or was delayed.

This method simplifies the timezone calculation for the API call and offloads the 'what constitutes a day' logic to your spreadsheet, where you can easily filter and sort by gmt_create (which is UTC) or a converted local time column.

Example Google Sheets Formula for Local Time Conversion

If your gmt_create column (which is UTC) is in A2, and you want to display it in BST/GMT:

=A2 + TIMEVALUE("01:00:00") (for BST, UTC+1)

Or, more robustly, if your spreadsheet's timezone is set correctly, simply formatting the cell as a date/time will often display it in the sheet's timezone. However, for precise calculations, explicit conversion is safer.

Final Considerations

API Documentation: Always verify the API's expected timezone for date parameters. AliExpress typically uses UTC for gmt_create and similar fields.
Idempotency: Ensure your process can handle pulling the same orders multiple times without causing issues (e.g., by de-duplicating in your spreadsheet or database). This is why the rolling lookback window is powerful.
DST Transitions: Be especially vigilant around the spring and autumn DST changes. These are the times when a simple '24 hours ago' calculation will fail most spectacularly.

By understanding and explicitly handling timezones, you can eliminate the BST-UTC date-filter bug, ensuring your order tracking is accurate and complete, no matter when or where your automation runs.

For more advanced tracking and automation features, explore Fetch Order Tracking.

Try the fetcher → More guides