Timezone-Aware Date Filtering for DateTime Fields (Fix “Equals” Operator Behavior)

Description

Problem Statement

Customers are experiencing inconsistent and misleading results when applying date-based filters (especially using the “equals” operator) on DateTime fields such as Created Date in Apsona.

This issue arises because:

  • Salesforce stores DateTime values in UTC (GMT)
  • Apsona evaluates date filters based on UTC boundaries, not the user’s or org’s timezone

As a result:

  • Records created later in the day (in local timezone) are shifted to the next UTC date

  • Filtering for a single date (e.g., Created Date = 28/08/2023) excludes valid records from that day

  • Records appear in incorrect date buckets (e.g., records from 28th appearing under 29th)

    • *

Customer Impact

  • Incorrect record counts when using “equals” filter
  • Data inconsistency and loss of trust in reporting
  • Users unknowingly working with incomplete or incorrect datasets

  • Current workaround (date-time range filters) is:

    • Not intuitive for end users
    • Not scalable for quick filtering use cases
    • Still fails in edge cases (e.g., cross-day UTC spillover from previous day)

    Example Scenarios

  1. Mismatch in same-day filtering
    • Filtering: Created Date = 28/08/2023
    • Expected: 501 records
    • Actual: 334 records
    • Missing records: Those created after ~2 PM IST (shifted to next UTC day)
  2. Cross-date contamination
    • Filtering: Created Date = 29/08/2023
    • Result includes records actually created on 28/08/2023 (late evening IST)

  3. Range filter limitation

    • Using:
      • Created Date ≥ 02/01/2026
      • Created Date < 02/02/2026
    • Still includes records from 01/31/2026 (late evening local time → stored as 02/01 UTC)

    Expected Behavior

Date-based filters (especially “equals”) should:

  • Respect the Salesforce org timezone (or optionally user timezone)
  • Return all records that fall within the selected date in that timezone
  • Behave consistently with how Salesforce UI reports and list views interpret dates