Timestamps Have a One-Hour Discrepancy During Daylight Saving Time

Issue

The timestamps for data synced to my destination during daylight saving time are off by one hour.

Environment

All destinations

Resolution

To correct the data in your destination, do the following:

1. Determine the daylight saving time start and end dates for your time zone.
2. In your destination, identify the impacted data.
3. Execute a query to adjust the applicable records' timestamps by one hour. For example, see the following example SQL query that adjusts data impacted by daylight saving time shifts in the Eastern Time Zone:

   SELECT
   ISSUE_ID,
   CASE
   WHEN CREATED BETWEEN ‘2024-03-10 02:00:00’ AND ‘2024-11-03 02:00:00’ THEN CREATED + INTERVAL ‘1 hour’
   ELSE CREATED
   END AS ADJUSTED_CREATED,
   CASE
   WHEN UPDATED BETWEEN ‘2024-03-10 02:00:00’ AND ‘2024-11-03 02:00:00’ THEN UPDATED + INTERVAL ‘1 hour’
   ELSE UPDATED
   END AS ADJUSTED_UPDATED,
   CASE
   WHEN RESOLVED BETWEEN ‘2024-03-10 02:00:00’ AND ‘2024-11-03 02:00:00’ THEN RESOLVED + INTERVAL ‘1 hour’
   ELSE RESOLVED
   END AS ADJUSTED_RESOLVED
   FROM
   table name;
   

To ensure data syncs to your destination correctly in the future, do the following:

1. In Fivetran, go to your destination page.
2. In the Action drop-down, select Edit connection details.
3. Set the Shift my UTC offset with daylight saving time (US Only) toggle to ON.

Cause

This issue occurs when the Shift my UTC offset with daylight saving time (US Only) toggle on your destination connection settings page is set to OFF when the daylight saving time shift starts. See our Choosing your time zone documentation for more information.