Timezone And Datetime Pitfalls

Timezone and Datetime Pitfalls

This skill activates when a practitioner encounters bugs or uncertainty around how Salesforce stores, filters, and displays Date and Datetime values across timezones. It covers the full stack from database storage (always UTC) through Apex runtime behavior, SOQL date literals, and UI rendering — and explains why the same value can appear as a different date depending on who is looking at it and from where.

Before Starting

Gather this context before working on anything in this domain:

• Identify whether the field in question is a Date type or a Datetime type — they have fundamentally different timezone semantics and the fix for one does not apply to the other.
• Determine the org's default timezone (Setup > Company Information), the running user's timezone (User record), and the timezone of any external system sending or receiving the value.
• Identify where the bug surfaces: in a report, in Apex logic, in a SOQL filter, in a Flow, or in a UI component. Each layer applies timezone rules differently.
• Check whether Daylight Saving Time (DST) is in scope: scheduled Apex fire times and fixed-offset calculations both shift at DST boundaries.

Core Concepts

Datetime Fields Are Stored as UTC, Displayed in User Timezone

Every Datetime field in the Salesforce database is stored as a UTC value. When Salesforce displays a Datetime in the UI (record detail, list view, report), it converts UTC to the viewing user's timezone automatically. This means the same stored value can show as Monday for a user in New York and as Tuesday for a user in Tokyo.

Apex Datetime values in memory are also UTC-referenced internally, but Datetime.format() with no arguments renders using the running user's locale and timezone. If you format a Datetime in an Apex context running as a system user (anonymous Apex, scheduled jobs), the output reflects that system user's timezone setting, which may differ from the user whose data you are processing.

// Datetime stored in DB: 2025-06-15T02:00:00Z
// Running user timezone: America/New_York (UTC-4 in summer)
Datetime dt = [SELECT CreatedDate FROM Account LIMIT 1].CreatedDate;
System.debug(dt.format()); // Prints "6/14/2025, 10:00 PM" — the previous calendar day

Date Fields Have No Timezone — And That Is the Pitfall

Date fields store a calendar date without any time component. Salesforce persists them as midnight UTC of that date (2025-06-15T00:00:00Z). There is no timezone conversion applied on read or write — the date is always the literal day string stored.

The critical consequence: Date.today() in Apex returns the server-side date, which is determined by the Salesforce server's clock in UTC. For a user in UTC+10 at 11:00 PM, the server is still on the previous calendar day. Date.today() will return yesterday's date relative to the user. This is one of the most common sources of off-by-one-day bugs in scheduled jobs and trigger logic.

// Server UTC time: 2025-06-14T23:30:00Z
// User local time (UTC+10): 2025-06-15T09:30:00 (next day)
Date serverToday = Date.today(); // Returns 2025-06-14 — wrong from the user's perspective

To get a Datetime representing the user's local "now", use Datetime.now() (which is UTC-equivalent) and then apply addSeconds(UserInfo.getTimeZone().getOffset(Datetime.now()) / 1000) to shift to local time before extracting a Date.

SOQL Date Literals Use the Running User's Timezone

SOQL date literals (TODAY, LAST_WEEK, LAST_N_DAYS:30, THIS_MONTH) are evaluated relative to the running user's timezone. The Salesforce SOQL engine converts the date literal boundaries to UTC ranges before executing the query.

This means the same SOQL statement can return different record sets depending on who runs it. A record created at 2025-06-15T01:00:00Z will match TODAY for a user in UTC+3 (their local date is June 15) but will match YESTERDAY for a user in UTC-5 (their local date is still June 14).

In Apex, the running user for a SOQL query is determined by whether the code runs in user mode or system mode — but the timezone used for date literals is still the running user's timezone profile, not the server timezone.

SOQL convertTimezone() Buckets dateTime Fields in the User's Timezone

SOQL returns dateTime field values as UTC by default. The convertTimezone() function converts a dateTime field to the running user's timezone so that date and hour grouping reflects the user's local calendar rather than UTC. It is the SOQL-native counterpart to the offset-shifting you would otherwise do in Apex before extracting a date.

Two constraints make this function easy to misuse:

• It cannot stand alone. convertTimezone() is only valid nested inside a SOQL date function — for example HOUR_IN_DAY(convertTimezone(CreatedDate)) or DAY_ONLY(convertTimezone(CreatedDate)). SELECT convertTimezone(CreatedDate) is not a valid query.
• The wrapping date function must be repeated verbatim in GROUP BY. Any date function in the SELECT clause must also appear identically in GROUP BY, and that rule includes the convertTimezone() call nested inside it.

// Opportunities bucketed by the hour of day they were created, in the user's timezone
AggregateResult[] byHour = [
    SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)) hourBucket, SUM(Amount) total
    FROM Opportunity
    GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))
];

HOUR_IN_DAY() and DAY_ONLY() — the functions most commonly wrapped around convertTimezone() — operate only on dateTime fields, not Date fields. DAY_ONLY() returns the date portion of the dateTime; without convertTimezone() that date portion is the UTC date, the same off-by-one trap as .date() in Apex (see Gotcha 3 in references/gotchas.md).

Datetime.format() and Timezone-Aware Formatting

Datetime.format() with no arguments uses the running user's locale and timezone. To produce a timezone-explicit string, use Datetime.format(String dateFormat, String timezone). To convert a Datetime to a different timezone for display or arithmetic, use TimeZone.getTimeZone(String id) combined with Datetime.format().

Datetime utcNow = Datetime.now();
// Format in a specific timezone regardless of running user:
String pacificTime = utcNow.format('yyyy-MM-dd HH:mm:ss', 'America/Los_Angeles');
String tokyoTime   = utcNow.format('yyyy-MM-dd HH:mm:ss', 'Asia/Tokyo');

The timezone ID strings follow the Java TimeZone naming convention (e.g., 'America/Chicago', 'Europe/London', 'UTC'). Passing 'UTC' forces the output to show the raw stored UTC value.

Common Patterns

Safe "Today" for the Running User

When to use: Any Apex logic that needs the current calendar date from the user's perspective (not the server's), such as populating a Date field, comparing against a deadline, or filtering with a dynamic SOQL date value.

How it works:

// Get the running user's timezone
TimeZone tz = UserInfo.getTimeZone();
// Shift Datetime.now() by the user's UTC offset to get their local date
Datetime localNow = Datetime.now().addSeconds(tz.getOffset(Datetime.now()) / 1000);
Date userToday = localNow.date();

Note: TimeZone.getOffset() returns milliseconds, so divide by 1000 for seconds. This approach handles DST correctly because getOffset() is evaluated at the given instant, not a fixed offset.

Why not Date.today(): Date.today() uses the server's UTC date, which can differ from the user's local calendar date by one day near midnight.

Storing Datetime from External Systems

When to use: Inbound API calls, integration middleware, or external event systems that send timestamps in a local timezone (e.g., 2025-06-15T14:30:00-07:00).

How it works:

Always convert incoming timestamps to UTC before storing in a Datetime field. Use Datetime.valueOf(String iso8601) which parses ISO 8601 with timezone offsets correctly. Never strip the offset and store the local time as if it were UTC — this introduces a systematic offset error equal to the source timezone.

// Incoming from external system: "2025-06-15T14:30:00-07:00"
// Datetime.valueOf parses the offset and stores the equivalent UTC value
Datetime utcValue = (Datetime) JSON.deserialize('"2025-06-15T14:30:00-07:00"', Datetime.class);
// Stored as 2025-06-15T21:30:00Z in the database
myRecord.EventTime__c = utcValue;

Alternatively, use Datetime.newInstanceGmt(year, month, day, hour, minute, second) when you already know the UTC components.

Why not Datetime.newInstance(year, month, day, hour, minute, second): The no-suffix variant uses the local (server) timezone, not UTC, producing incorrect UTC storage if the server timezone is not UTC.

Avoiding Date Field Midnight UTC Crossing

When to use: Reports, dashboards, or Apex that groups or compares Date field values for users in timezones far from UTC (UTC+10 to UTC+12, or UTC-8 to UTC-10).

How it works:

Recognize that Date fields display the stored date string directly with no conversion. If your process populates a Date field from a Datetime (myDate = myDatetime.date()), the resulting date is the UTC date of that Datetime, not the local date. For users in UTC+10, a Datetime of 2025-06-15T23:00:00Z converts to 2025-06-16 local time but .date() returns 2025-06-15.

To store the user's local calendar date:

TimeZone tz = UserInfo.getTimeZone();
Datetime localDt = myDatetime.addSeconds(tz.getOffset(myDatetime) / 1000);
Date localDate = localDt.date();

Time-Zone-Correct Day or Hour Bucketing in SOQL

When to use: Aggregate reporting queries that count or sum records grouped by the user's local calendar day or hour — "opportunities created by hour", "cases opened per day" — without pulling every raw row into Apex to re-bucket.

How it works:

Wrap the dateTime field in convertTimezone(), wrap that in the date function you are grouping by, and repeat the whole expression in GROUP BY:

AggregateResult[] casesByDay = [
    SELECT DAY_ONLY(convertTimezone(CreatedDate)) localDay, COUNT(Id) cnt
    FROM Case
    WHERE CreatedDate = LAST_N_DAYS:30
    GROUP BY DAY_ONLY(convertTimezone(CreatedDate))
    ORDER BY DAY_ONLY(convertTimezone(CreatedDate))
];

Why not bare DAY_ONLY(CreatedDate): Without convertTimezone() the date portion is extracted in UTC, so records created in the evening UTC hours land in the wrong local-day bucket for users east of UTC. Wrapping with convertTimezone() shifts the bucketing to the running user's timezone inside the SOQL engine, so you avoid querying raw rows and re-bucketing in Apex.

Tradeoff: Because convertTimezone() uses the running user's timezone, an aggregate refreshed by users in different timezones can still produce different buckets. When you need a single fixed timezone regardless of who runs the query, compute explicit UTC range boundaries in Apex instead (see Example 2 in references/examples.md).

Decision Guidance

Recommended Workflow

1. Identify the field type and the failure layer. Determine whether the bug involves a Date field, a Datetime field, or the boundary between them. Identify where the wrong value appears: in Apex logic, SOQL results, the UI, or a Flow comparison.
2. Map all timezone contexts in play. List: the Salesforce server timezone (UTC), the running user's profile timezone, the external system's timezone (if any), and the timezone implicit in any date literals or hardcoded dates.
3. Trace the value lifecycle. Follow the value from origin (creation, inbound API, user input) through storage (always UTC for Datetime), through any Apex arithmetic, through SOQL filtering, and through display. At each step identify whether a timezone conversion is happening explicitly, implicitly, or not at all.
4. Apply the appropriate fix pattern. Use the Decision Guidance table to select the right approach. For Date.today() bugs use the user-local today pattern. For SOQL date literal bugs use explicit bind variables. For display bugs use Datetime.format() with an explicit timezone string.
5. Test with a user in a UTC+ timezone and a UTC- timezone. Most timezone bugs only manifest for users far from UTC. Add test cases that simulate a System.runAs() for a user with a non-UTC timezone.
6. Verify DST boundary behavior. If the logic runs on or near a DST transition date (e.g., second Sunday in March or first Sunday in November for US timezones), test with a Datetime value that crosses the boundary to confirm offset recalculation works correctly.

Review Checklist

• No Date.today() calls in code that needs the running user's local calendar date
• All Datetime.newInstance() calls have been reviewed; UTC variants (newInstanceGmt) used for external data
• SOQL date literals (TODAY, THIS_WEEK, etc.) are intentional; dynamic SOQL using explicit ranges used where timezone-independence is required
• Datetime.format() calls include an explicit timezone string where output timezone matters
• Datetime.date() calls that feed Date fields have been verified to extract the correct local date, not the UTC date
• Aggregate SOQL that buckets by local day/hour wraps the field in convertTimezone() (nested inside DAY_ONLY()/HOUR_IN_DAY() and repeated in GROUP BY), or documents that UTC bucketing is intentional
• Scheduled Apex fire times are calculated in UTC using the target date's offset, not a fixed offset
• Test classes include at least one System.runAs() with a non-UTC timezone user for any date/time-sensitive logic

Salesforce-Specific Gotchas

1. Date.today() uses the server clock, not the user clock — Apex runs on Salesforce servers set to UTC. Date.today() returns the UTC calendar date at the moment of execution. A user in UTC+12 at 1:00 AM is already on the next calendar day, but Date.today() returns yesterday's date. This silently breaks due-date logic, deadline comparisons, and any Date field auto-population that should reflect the user's local date.

2. Datetime.newInstance() silently uses the server local timezone — The four-to-seven-argument Datetime.newInstance(year, month, day[, hour, minute, second]) creates a Datetime in the Salesforce server local timezone (which is UTC in practice, but this is an implementation detail, not a contract). The explicitly UTC-safe method is Datetime.newInstanceGmt(year, month, day, hour, minute, second). For code that must be portable and explicit, always use the GMT variant.

3. SOQL date literals shift the result set based on running user — Running the same SOQL in anonymous Apex as a System Administrator and in a trigger running as a field rep in Tokyo produces different record sets if the query uses date literals like TODAY or LAST_N_DAYS:7. The Salesforce SOQL engine converts literals to UTC ranges using the running user's timezone profile. This means report results can change depending on who refreshes the report.

4. Datetime.date() extracts the UTC date, not the local date — Calling .date() on a Datetime strips the time component and returns the UTC date. If the Datetime is 2025-06-15T23:00:00Z and the running user is in UTC+10 (local time: June 16, 09:00), .date() returns 2025-06-15, not 2025-06-16. Any code that populates a Date field from a Datetime using .date() will show the wrong calendar day for users east of UTC when the Datetime is in the evening UTC hours.

5. Scheduled Apex and DST: fixed offsets break twice a year — If a scheduled job is configured to fire at a target wall-clock time (e.g., "9 AM Eastern") by computing a UTC equivalent using a fixed offset (-5 for EST), that offset becomes wrong when DST is in effect (-4 for EDT). The job will fire at 10 AM Eastern during summer. Always compute the UTC equivalent dynamically using TimeZone.getOffset(targetDatetime) at the target date, not a static offset.

Output Artifacts

Related Skills

• apex/apex-scheduling — scheduling Apex jobs at correct wall-clock times accounting for DST
• data/data-reconciliation-patterns — when datetime mismatches cause record duplication or missed matches during data loads