How to Convert a Timestamp to a Date in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
DATE(timestamp_col) truncates a TIMESTAMP to its UTC calendar date. Pass a time zone - DATE(ts, "Europe/Helsinki") - to get the local date instead, and convert Unix epoch numbers with TIMESTAMP_SECONDS or TIMESTAMP_MILLIS first.
SELECT
DATE(created_at) AS utc_date,
DATE(created_at, 'Europe/Helsinki') AS local_date
FROM orders;The time zone argument is the whole point
A TIMESTAMP is an absolute instant; which calendar day it falls on depends on the zone. DATE(ts) and CAST(ts AS DATE) both assume UTC, which silently shifts late-evening events to the next day for anyone east of it. Grouping daily numbers by local date is usually what the business means.
SELECT
DATE(created_at, 'America/New_York') AS day,
COUNT(*) AS orders
FROM orders
GROUP BY day
ORDER BY day;From Unix epoch numbers
Epoch integers are not timestamps yet. TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, and TIMESTAMP_MICROS convert them; UNIX_SECONDS and friends go the other way.
SELECT
DATE(TIMESTAMP_SECONDS(event_epoch)) AS event_date,
UNIX_SECONDS(created_at) AS created_epoch
FROM raw_events;Related conversions
EXTRACT(DATE FROM ts) is equivalent to DATE(ts) and also accepts AT TIME ZONE. DATETIME(ts, zone) keeps the time-of-day part when you need a local wall-clock value rather than just the day.
SELECT
EXTRACT(DATE FROM created_at AT TIME ZONE 'Europe/Helsinki') AS local_date,
DATETIME(created_at, 'Europe/Helsinki') AS local_datetime
FROM orders;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free