Problem

You want to run a SQL query using CAST(timestamp_column AS DATE) after setting a session time zone (for example, SET TIME ZONE 'Area/City';). 

When you use a simple SELECT query, you notice it returns the correct local date based on the configured time zone. However, using the same expression inside a GROUP BY or WHERE clause produces different date boundaries, often corresponding to UTC dates instead of local time zone dates.

Example

The following code example specifically uses ‘Asia/Manila’ and then illustrates the two query types described in the previous section. Note that you can apply any region-based zone ID (Area/City). 

SET TIME ZONE 'Asia/Manila';

-- Query 1
SELECT created_at, CAST(created_at AS DATE)
FROM <table>
WHERE CAST(created_at AS DATE) BETWEEN DATE '<yyyy-mm-dd>' AND DATE '<yyyy-mm-dd>'
ORDER BY CAST(created_at AS DATE);

-- Query 2
SELECT CAST(created_at AS DATE), COUNT(*)
FROM <table>
WHERE CAST(created_at AS DATE) BETWEEN DATE '<yyyy-mm-dd>' AND DATE '<yyyy-mm-dd>'
GROUP BY 1
ORDER BY 1;

Query 1 returns results based on Asia/Manila local time. Query 2 groups rows based on UTC date boundaries, leading to inconsistent results.

Cause

The command SET TIME ZONE 'Asia/Manila' affects how timestamps are displayed, not how they are stored or processed internally.

In Databricks SQL, timestamps are always stored as UTC instants.

The discrepancy arises because of how Apache Spark optimizes expressions.

• In a SELECT clause, the CAST operation is applied after adjusting the timestamp to the session time zone.
• In a GROUP BY or WHERE clause, the optimizer applies the CAST before time zone conversion, effectively truncating at UTC midnight instead of local midnight.

This causes the same timestamp to map to different dates depending on the context of the query.

Solution

To ensure consistent behavior across all SQL operations, explicitly perform a timezone-aware conversion before casting to DATE using the from_utc_timestamp() function.

SELECT
  CAST(from_utc_timestamp(created_at, 'Asia/Manila') AS DATE) AS local_date,
  COUNT(*)
FROM <table>
WHERE CAST(from_utc_timestamp(created_at, 'Asia/Manila') AS DATE)
      BETWEEN DATE '<yyyy-mm-dd>' AND DATE '<yyyy-mm-dd>'
GROUP BY local_date
ORDER BY local_date;

This ensures that the timestamp is first converted from UTC to Asia/Manila time.

The CAST(... AS DATE) then respects local midnight boundaries, ensuring consistent filtering and grouping.