Backend Date and Time Handling: UTC, Time Zones and Query Boundaries

An order created at 00:15 in Vietnam belongs to the previous calendar date when represented in UTC. Filtering a Vietnamese daily report by the UTC date can therefore produce a successful query with incorrect results. Time-related bugs often originate in semantics and boundaries rather than formatting functions.

This guide models temporal data and converts a local date into UTC bounds with PHP 8.2 or later. The PostgreSQL query is illustrative: test it with your schema, driver and data before deployment.

1. Separate instants, dates and recurring schedules

Storing instants in UTC does not replace modeling birthdays or recurring schedules. For future schedules, retain a zone identifier such as Asia/Ho_Chi_Minh and the user's intent. A numeric offset alone cannot describe every region's rules.

2. Define the API and database contract

For instant fields, require an explicit offset: 2026-09-22T00:15:00+07:00 and 2026-09-21T17:15:00Z represent the same instant. Reject missing offsets unless the contract defines their interpretation. Date-only fields use YYYY-MM-DD without automatic zone conversion.

The PostgreSQL documentation explains that timestamptz stores an instant in UTC without retaining its original zone name; display uses the session timezone. Store the original zone separately when needed. timestamp without time zone has different semantics.

Make database connection, worker and logging conventions explicit. Before modifying apparently shifted historical values, establish whether the underlying data is wrong or merely displayed in another zone.

3. Query a local day with a half-open interval

September 22, 2026 in Vietnam runs from 2026-09-21T17:00:00Z up to, but not including, 2026-09-22T17:00:00Z. Use start <= created_at < end.

An end time of 23:59:59 can miss fractional-second records. Adjacent half-open intervals share a boundary without double-counting its records. Do not assume every local calendar day lasts 86,400 seconds when supporting daylight-saving regions.

4. PHP: validate the date and convert its boundaries

createFromFormat can normalize out-of-range dates. Validate the shape, warnings and round-trip result. The ! resets time fields; getLastErrors can return false when parsing has no errors on PHP 8.2 onward.

<?php
declare(strict_types=1);

function utcDayBounds(string $day, string $zone): array
{
    if (!preg_match('/\A[0-9]{4}-[0-9]{2}-[0-9]{2}\z/', $day)) {
        throw new InvalidArgumentException('Expected YYYY-MM-DD');
    }
    $local = DateTimeImmutable::createFromFormat(
        '!Y-m-d', $day, new DateTimeZone($zone)
    );
    $errors = DateTimeImmutable::getLastErrors();
    if ($local === false
        || ($errors !== false && ($errors['warning_count'] || $errors['error_count']))
        || $local->format('Y-m-d H:i:s') !== $day.' 00:00:00') {
        throw new InvalidArgumentException('Invalid date or unsupported midnight');
    }
    $end = $local->modify('+1 day');
    if ($end->format('H:i:s') !== '00:00:00') {
        throw new InvalidArgumentException('Unsupported midnight transition');
    }
    $utc = new DateTimeZone('UTC');
    return [$local->setTimezone($utc), $end->setTimezone($utc)];
}

[$start, $end] = utcDayBounds('2026-09-22', 'Asia/Ho_Chi_Minh');
echo $start->format(DateTimeInterface::ATOM), PHP_EOL;
echo $end->format(DateTimeInterface::ATOM), PHP_EOL;

The output is 2026-09-21T17:00:00+00:00 followed by 2026-09-22T17:00:00+00:00. Calculate the next calendar day in the local zone before converting both boundaries to UTC.

This example targets ordinary Vietnamese dates and the New York fixtures below, not every historical timezone transition. It rejects midnight normalized to a different time. Worldwide applications need additional policy and tests for skipped dates or ambiguous midnights. Translate invalid zone exceptions into appropriate API validation errors too.

5. Bind PostgreSQL query parameters

SELECT id, created_at
FROM orders
WHERE created_at >= CAST(:start AS timestamptz)
  AND created_at < CAST(:end AS timestamptz)
ORDER BY created_at, id;

This assumes a timestamptz column. Bind :start and :end to the offset-bearing strings produced by PHP; do not concatenate user input into SQL. Add tenant or owner restrictions in multi-tenant applications: date filtering does not provide authorization.

Comparing the original column allows a suitable index to be considered, but does not guarantee the planner will select it. Inspect execution plans using representative data before making performance claims. Avoid converting every row merely to derive boundaries that can be computed once.

6. Build boundary-focused tests

• Verify the Vietnamese example's UTC boundaries and 24-hour duration.
• Reject 2026-02-30, missing leading zeros and trailing characters.
• Accept 2024-02-29; reject 2026-02-29.
• Under the timezone rules used by the fixtures, America/New_York has a 23-hour day on 2026-03-08 and a 25-hour day on 2026-11-01.
• Include a record exactly at start; exclude one exactly at end; include one just before end.
• Run database integration tests with different session timezones and verify consistent meaning.

Keep the environment's timezone database updated. For recurring schedules in daylight-saving regions, explicitly decide whether a nonexistent time is skipped or moved and whether a repeated time runs once or twice. Do not accidentally turn library defaults into business rules.

7. Implementation checklist

• Classify every temporal field as an instant, date, schedule or duration.
• Choose reporting zones from user or organization policy, not implicit server settings.
• Validate input strictly and pass zones explicitly.
• Use half-open intervals, bound parameters and authorization restrictions.
• Test leap days, clock transitions and fractional-second boundaries.
• Distinguish conversion-function tests from real database query tests.

Reliable date handling starts by asking what a value means. Explicit semantics, zones and boundaries make backend behavior easier to test and prevent subtle daily-report errors.