Timezone Mismatch Between DAL Entities and Raw SQL Queries in Custom Reports

Context

Shopware 6 stores all date-time values in UTC and relies on the Data Abstraction Layer (DAL) to convert timestamps to the configured system timezone during entity retrieval. This conversion is applied transparently when using repository methods or the storefront API.

Problem

Custom reporting queries that bypass the DAL and read directly from tables such as order or order_line_item receive raw UTC timestamps. When these values are rendered alongside DAL-sourced data in the same report, a systematic offset appears equal to the difference between UTC and the administration timezone setting. The discrepancy is not an error in storage but a consequence of skipping the application-level conversion layer.

Constraints

The system timezone is configurable per installation and can differ from the server's operating system timezone. Storefront-specific timezones add another dimension for multi-region deployments. Any solution must remain compatible with Shopware 6.5+ entity definitions that use DateTimeField and must not require schema changes.

Questions

1. What is the recommended pattern for applying the active system timezone offset inside a raw SQL query or a custom database view so that results match DAL-hydrated entities?
2. Can the timezone identifier be retrieved programmatically from the Shopware configuration within a database migration or reporting service without booting the full kernel?