Timestamps off by hours in Azure Databricks: session time zones, TIMESTAMP vs TIMESTAMP_NTZ and daily totals that shift

#Symptom

One of these usually lands in the ticket:

• An event that happened at 09:00 in the source system shows as 08:00, 10:00 or 14:00 in a Databricks table.
• The same query returns different times on a cluster and on a SQL warehouse, or for two different users.
• Daily totals don't match the source. A few hours of rows have moved from one day to the next.
• The error moves by one hour twice a year, at daylight saving changes.

There are two possible causes. Either the value was stored wrong when it was ingested, or it is stored correctly and is being displayed or bucketed in a different time zone. Work out which before you change anything.

#How Databricks handles timestamps

The session time zone is spark.sql.session.timeZone on clusters and the TIMEZONE parameter on SQL warehouses. The system default is UTC, but someone can change it on a cluster, in a notebook, in a job or across the whole warehouse estate.

Two consequences:

1. A string with no offset, such as '2026-11-03 09:00:00', is read as local time in the session time zone when you cast it to TIMESTAMP. If the source system meant Europe/London and the session is UTC, the stored instant is wrong for half the year.
2. The same stored TIMESTAMP displays differently in sessions with different time zones. The data hasn't changed. Only the rendering has.

#Checks

#1. What time zone is this session using?

SELECT current_timezone();
SET spark.sql.session.timeZone;   -- clusters
SET TIMEZONE;                     -- SQL warehouses

Run this on the cluster, on the warehouse and in the job that wrote the data. If you get different answers, that explains the "different results for different users" case.

On a cluster, also look at the Spark config (Compute > cluster > Advanced options > Spark) for spark.sql.session.timeZone. In the job's notebook or wheel, search for SET TIME ZONE or spark.conf.set("spark.sql.session.timeZone".

#2. What type is the column?

DESCRIBE TABLE prod.sales.orders;
-- or across a schema
SELECT table_name, column_name, data_type
FROM prod.information_schema.columns
WHERE table_schema = 'sales' AND data_type LIKE 'TIMESTAMP%';

#3. Is the stored value right or just the display?

Compare the column with explicit renderings that don't depend on the session:

SELECT order_id,
       order_ts,                                            -- session time zone
       date_format(order_ts, 'yyyy-MM-dd HH:mm:ss XXX') AS with_offset,
       unix_timestamp(order_ts)                         AS epoch_seconds,
       from_utc_timestamp(order_ts, 'Europe/London')    AS london_wall_clock
FROM prod.sales.orders
WHERE order_id = 'A-10023';

Convert epoch_seconds with any trusted tool and compare it with the source system's record of that event in UTC. If they match, the data is fine and you have a display or bucketing problem (fix A). If they don't, the value was stored wrong at ingest (fix B).

#4. Does the error follow daylight saving?

If the gap is one hour in summer and zero in winter (or the reverse), the source sent local wall-clock time without an offset and it was parsed as UTC. A constant gap all year usually means a fixed-offset zone was used somewhere, or the source really is in a zone without daylight saving.

#Symptom to cause to fix

#Fix A: standardise the session time zone

Pick UTC for pipelines, set it explicitly everywhere, and only convert to a local zone in the final presentation layer.

On clusters, add this to the Spark config (or fix it in a compute policy so nobody can change it):

spark.sql.session.timeZone UTC

On a SQL warehouse, an admin can set TIMEZONE as a SQL configuration parameter for all warehouses in Settings > Compute > SQL warehouses (location varies by UI version). A single session can override it:

SET TIME ZONE 'UTC';
SET TIME ZONE LOCAL;   -- back to the TIMEZONE parameter value

Use region IDs such as Europe/London or America/New_York, not fixed offsets like +01:00. Fixed offsets ignore daylight saving.

Warning

Changing the cluster or warehouse time zone changes the output of every query that formats, truncates or casts timestamps, including dashboards and downstream extracts. Announce the change and compare key reports before and after.

#Fix B: parse source strings in the right zone

If the source sends wall-clock time for a known zone, say so when you parse:

from pyspark.sql import functions as F

df = (spark.read.table("prod.bronze.orders_raw")
      .withColumn("order_ts_local", F.to_timestamp("order_time_str", "yyyy-MM-dd HH:mm:ss"))  # parsed as session zone (UTC)
      .withColumn("order_ts", F.to_utc_timestamp("order_ts_local", "Europe/London")))         # now the real instant

If the strings carry an offset (2026-11-03T09:00:00+01:00), include it in the pattern (XXX) and Spark stores the correct instant whatever the session zone.

To repair rows already stored wrong, rewrite the affected range from bronze rather than adding hours with arithmetic. Arithmetic gets the hours around DST changes wrong.

MERGE INTO prod.silver.orders t
USING (
  SELECT order_id,
         to_utc_timestamp(to_timestamp(order_time_str, 'yyyy-MM-dd HH:mm:ss'), 'Europe/London') AS order_ts
  FROM prod.bronze.orders_raw
  WHERE ingest_date >= '2026-10-01'
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.order_ts = s.order_ts;

Caution

Run the repair on a clone or a filtered test first and record the table version (DESCRIBE HISTORY) before the MERGE, so you can RESTORE if the result is wrong.

#Fix C: bucket business days in the business zone

When finance wants "orders per day, London time", don't depend on the session zone:

SELECT to_date(from_utc_timestamp(order_ts, 'Europe/London')) AS business_date,
       count(*) AS orders
FROM prod.silver.orders
GROUP BY 1;

Store business_date as a column in gold tables so every consumer gets the same answer.

#When to use TIMESTAMP_NTZ

Use TIMESTAMP_NTZ for values that are wall-clock by nature and must never shift, such as a store's opening time, a scheduled appointment or a source system's local time that you keep for audit. Keep real event instants as TIMESTAMP.

CREATE TABLE prod.ops.store_hours (store_id STRING, opens_at TIMESTAMP_NTZ);

-- convert between zones (DBR 13.3 LTS and above); returns TIMESTAMP_NTZ
SELECT convert_timezone('Europe/London', 'UTC', opens_at) FROM prod.ops.store_hours;

Creating a new Delta table with a TIMESTAMP_NTZ column enables the timestampNtz table feature automatically. Adding one to an existing table doesn't, so you enable it first:

ALTER TABLE prod.sales.orders SET TBLPROPERTIES ('delta.feature.timestampNtz' = 'supported');

Warning

Enabling the timestampNtz feature upgrades the table protocol. Older Spark versions and some external readers can then no longer read the table. Check every consumer before you turn it on.

You can't change an existing column from TIMESTAMP to TIMESTAMP_NTZ in place. Add a new column, backfill it, switch consumers over, then drop the old column.

#Prevent it next time

• Set spark.sql.session.timeZone UTC in every compute policy and the TIMEZONE parameter on warehouses, so nobody depends on defaults.
• At ingest, record the source system's zone in the pipeline config and always parse with an explicit offset or zone.
• Store event times as TIMESTAMP (UTC instants) and wall-clock values as TIMESTAMP_NTZ, and name columns to match, for example order_ts_utc and local_opening_time.
• Materialise business_date in gold tables rather than deriving it in each report.
• Add a data quality check that compares daily counts per business date with the source system, and alert on drift. Problems that follow DST show up within a day of the clock change.