Basics

Database Management

Dates and Times

Analysis

BigQuery Date and Time Data Types

Google BigQuery provides several powerful date and time data types that allow you to store, process, and analyze temporal data efficiently. Understanding these types is essential for building accurate and performant queries.

Overview of Date and Time Types

BigQuery offers the following core date and time types:

  • DATE: Represents a calendar date (year, month, day) without a time or time zone. Example: '2025-05-07'
  • DATETIME: Combines date and time, but without a time zone. Example: '2025-05-07 15:30:00'
  • TIME: Represents a time of day, independent of date and time zone. Example: '15:30:00'
  • TIMESTAMP: A specific point in time, with nanosecond precision and time zone awareness. Example: '2025-05-07 15:30:00 UTC'

When to Use Each Type

DATE

Use when you need to store only the date part. Ideal for birthdays, holidays, or event dates without time details.

DATETIME

Use for date and time together, but when the time zone doesn't matter — like store hours or internal logs.

TIME

Perfect for time-of-day fields like shift start times, open/close hours, or recurring schedules.

TIMESTAMP

Best for recording exact moments, such as event logs, transactions, or system updates, especially across time zones.

Practical Examples

-- DATE example
SELECT DATE '2025-05-07';

-- DATETIME example
SELECT DATETIME '2025-05-07 15:30:00';

-- TIME example
SELECT TIME '15:30:00';

-- TIMESTAMP example
SELECT TIMESTAMP '2025-05-07 15:30:00 UTC';

Conversions and Functions

BigQuery offers many functions for working with date and time types:

  • EXTRACT() — get parts of a date/time, like year or month.
  • FORMAT_TIMESTAMP() — format timestamps as strings.
  • PARSE_DATE() — convert strings to dates.
  • DATETIME(TIMESTAMP) — cast between types.

Example:

SELECT EXTRACT(YEAR FROM DATE '2025-05-07');

Conclusion

By choosing the right date and time type in BigQuery, you can improve data clarity, reduce errors, and write more efficient SQL queries. Familiarize yourself with these types and functions to get the most out of your temporal data.

Tip: Always store timestamps in UTC and convert to local time zones only when displaying data.