Date and Time Functions

Walk through practical date and time operations in BigQuery, focusing on extracting components, performing time arithmetic, and formatting temporal values using real query examples. These techniques are essential when analyzing time-based data from multiple sources or regions.

Extracting Date Components

BigQuery allows you to extract specific parts of a date using EXTRACT. EXTRACT(YEAR FROM sample_date) retrieves the year from a full date value.

This is commonly used for grouping, filtering, or aggregating data by year, month, or other time units.

Date and Time Arithmetic

BigQuery supports adding and subtracting intervals to adjust dates and times:

• DATE_ADD(sample_date, INTERVAL 5 DAY) shifts a date forward by a defined period;
• DATETIME_SUB(sample_datetime, INTERVAL 2 HOUR) subtracts time from a datetime value.

These operations are useful for calculating post-event windows, aligning time zones, or correcting ingestion and logging delays.

Formatting Date and Time Values

The FORMAT_DATETIME function converts datetime values into readable or custom formats. It allows transforming timestamps into formats like YYYY-MM-DD HH:MM.

This is especially helpful when preparing data for reports, dashboards, or downstream systems with specific formatting requirements.

Why It Matters

Time data often comes in different formats and levels of precision. Some sources include time zones or seconds, while others store only dates. Date and time functions make it possible to standardize this data, enable time-based analysis, and ensure consistent interpretation across datasets.

Mastering date and time functions in BigQuery allows you to transform raw temporal data into aligned, structured, and analysis-ready insights — an essential skill when working with global or multi-source datasets.