Timezones #

The starter kit stores timestamps in UTC and converts them only at display and date-filter boundaries.

UTC Storage Guarantee #

Keep Laravel's storage timezone pinned to UTC:

APP_TIMEZONE=UTC
APP_DISPLAY_TIMEZONE=Europe/Istanbul

APP_TIMEZONE controls config('app.timezone') and must remain UTC. Using a regional timezone here makes stored rows ambiguous and can apply the same offset twice when a value is formatted later. APP_DISPLAY_TIMEZONE is independent: it provides the site's display fallback without changing storage.

Run the storage check at any time:

php artisan sk:doctor --only=timezone-storage

The check fails when config('app.timezone') !== 'UTC'. Correct the configuration before writing more rows; rows already stored under a non-UTC configuration may need application-specific review because their intended instants are ambiguous.

Database Connection Timezone #

Keeping the application timezone at UTC is necessary, but it is not sufficient for MySQL or MariaDB. A TIMESTAMP column is converted from the connection session timezone to UTC on write, then converted back to the session timezone on read. If the session inherits SYSTEM and the database host clock is UTC+03:00, an application-supplied UTC wall-clock value is therefore stored three hours behind. The reverse conversion makes the value look correct to the application, while replicas with another session timezone, mysqldump output, and BI/reporting tools see the shifted instant on disk.

DATETIME columns do not receive this session conversion and are unaffected.

The contract for the kit's MySQL and MariaDB connections is a literal entry in each existing connection array in config/database.php:

'timezone' => '+00:00',

sk:install adds the entry when it is missing. It is deliberately not backed by an environment variable: there is exactly one correct storage value, and making it configurable would make the corruption configurable. Existing timezone values are never overwritten, missing connection arrays are skipped, and the SQLite, PostgreSQL, and SQL Server connections are not changed.

One-time conversion for existing data #

First take the branch that ends the investigation early. If the MySQL host clock has been UTC for the entire lifetime of the data, no bytes were shifted and no conversion is needed. Check the session setting and the clock it currently resolves to in one query:

SELECT @@session.time_zone AS session_time_zone,
       NOW() AS session_now,
       UTC_TIMESTAMP() AS utc_now,
       TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), NOW()) AS utc_offset_seconds;

An offset of 0 is enough only when the host itself is configured for UTC. If @@session.time_zone is SYSTEM, verify the operating-system/database-host timezone too: a regional zone can currently be at UTC while having observed a different daylight-saving offset for older rows.

If the inherited host clock was not UTC, do not run a blanket update. Take a restorable backup and rehearse the complete procedure on a copy of the database first. The kit cannot know the historical host offset. Determine it from the host configuration and deployment history, then verify the direction and amount against at least one known-good record whose real creation time can be confirmed independently. If the host observed daylight-saving changes or its timezone changed during the data's lifetime, separate the affected rows into periods and verify each period's offset.

Classify TIMESTAMP columns by how they were written, not by their names:

• Application-written TIMESTAMP values such as created_at, updated_at, last_login_at, email_verified_at, and revoked_at were stored with the inherited offset but appeared correct through the old session. After the connection is pinned to UTC, old values appear shifted and must be moved by the same signed offset that the old session inherited.
• Database-written DEFAULT CURRENT_TIMESTAMP values were stored at the correct instant by MySQL and only appeared offset-forward through the old session. The UTC connection setting fixes their display by itself. In this kit, exclude file_favorites.created_at and failed_jobs.failed_at from every conversion update. Updating them would corrupt values that are already correct.

Perform the real cutover in a maintenance window with application writes stopped: back up again, add 'timezone' => '+00:00', clear/purge long-lived connections, verify that a fresh session reports +00:00, and only then update the pre-existing application-written columns. The following is an adaptation example, not a ready-to-paste command:

-- EXAMPLE ONLY: the independently verified old offset was +03:00.
-- Adapt the tables, columns, signed interval, and legacy-row predicate.
START TRANSACTION;

UPDATE your_table
SET created_at = DATE_ADD(created_at, INTERVAL 180 MINUTE),
    updated_at = DATE_ADD(updated_at, INTERVAL 180 MINUTE)
WHERE your_verified_legacy_row_predicate;

-- Inspect known records before choosing COMMIT; use ROLLBACK if they do not match.

Use a negative interval for a negative inherited offset, and do not assume every table or historical period has the same write path. Prefer stopping writes and converting all pre-existing rows before traffic resumes. If the config change has already gone live, the dataset is mixed: rows written before the change are offset while rows written after it are correct. The conversion must reconcile only the old population, using a cutover marker or another independently verified predicate; a broad update would damage the new rows.

sk:upgrade does not perform this data conversion and never will. It can only rewrite config/database.php, after the safety assessment and consent prompt described in the update guide. The data-specific offset, column provenance, and row boundary cannot be inferred safely by the kit.

Display Timezone Resolution #

Every backend display boundary uses the same chain, exposed by resolve_display_timezone(?object $user = null): string:

1. user.timezone
2. config('app.display_timezone') — the Settings → General timezone
3. config('app.timezone')
4. 'UTC'

Invalid IANA timezone identifiers are skipped instead of causing an exception. The profile info tab and the admin user create/edit forms provide a searchable timezone selector.

The users.timezone column is nullable and has no default. null means follow the site setting; it is not equivalent to explicitly storing 'UTC'. A user with null follows a later General-settings timezone change, while a user who selected UTC stays on UTC.

Inertia shares the resolved timezone as the top-level timezone prop. auth.user.timezone remains the user's raw preference, including null.

Backend Date Helpers #

The two helpers serve different contracts:

format_date() keeps its existing display format and remains backward compatible. It now follows the shared resolution chain and accepts an explicit timezone override, but its result is not a parseable instant contract. API Resources must use to_api_date() so clients can safely reformat the value.

Frontend Formatting #

Import formatDateTime, formatDate, or formatTime from @lvntr/components/utils/datetime. They use Intl.DateTimeFormat with an explicit timezone resolved in this order:

1. the function's explicit timezone argument
2. the Inertia shared timezone prop
3. the browser timezone
4. 'UTC'

The utilities return '' for null or unparseable input. For upgrade compatibility, an existing dd-mm-yyyy HH:mm display string passes through unchanged; update consumer Resources to to_api_date() so new code receives ISO-8601 instead.

Datatable Date Filters #

Use DatatableQueryBuilder::dateRangeFilters($column) for date columns. The incoming Y-m-d value is a calendar date in the user's resolved display timezone, not a UTC date. The factory converts it to a half-open UTC range against the bare column:

column >= local-day start converted to UTC
column <  next local-day start converted to UTC

The exclusive next-day boundary includes the whole final day, including sub-second values. Computing it from the parsed local day also handles 23- and 25-hour DST days correctly. Because the query compares the unwrapped column rather than using whereDate, an index on that column remains usable.