Date and time functions in BigQuery are essential for performing operations such as extracting parts of a date, calculating the difference between dates, or manipulating dates and times. These functions help process temporal data for reporting, analytics, and scheduling tasks.
CURRENT_DATE() / CURRENT_TIMESTAMP()
- Explanation: These functions return the current date or timestamp in the respective data types.
- CURRENT_DATE(): Returns the current date in the format YYYY-MM-DD.
- CURRENT_TIMESTAMP(): Returns the current date and time (timestamp) in UTC, including the time zone and fractional seconds.
- Usage:
- CURRENT_DATE(): Provides the current date.
- CURRENT_TIMESTAMP(): Provides the current date and time.
- Example:
SELECT CURRENT_DATE() AS today_date;
This returns the current date, e.g., "2024-09-21".
SELECT CURRENT_TIMESTAMP() AS current_time;
This returns the current date and time with time zone information, e.g., "2024-09-21 13:45:23.123456 UTC".
- Practical Use:
These functions are commonly used to compare data against the current date or timestamp. For instance:
SELECT COUNT(*) AS recent_orders
FROM orders
WHERE order_date = CURRENT_DATE();
This query counts the number of orders placed today.
DATE_ADD() / TIMESTAMP_ADD()
- Explanation: These functions are used to add a specific time interval to a date or timestamp.
- DATE_ADD(date, INTERVAL n unit): Adds n units (e.g., days, months, years) to a date.
- TIMESTAMP_ADD(timestamp, INTERVAL n unit): Adds n units (e.g., minutes, hours, days) to a timestamp.
- Usage:
- DATE_ADD(date_column, INTERVAL n unit): Adds a time interval (like days or months) to a date.
- TIMESTAMP_ADD(timestamp_column, INTERVAL n unit): Adds a time interval (like seconds or hours) to a timestamp.
- Example:
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY) AS date_next_week;
This query adds 7 days to the current date, showing the date for the next week.
SELECT TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR) AS time_in_two_hours;
This query adds 2 hours to the current timestamp, showing what the time will be in two hours.
- Practical Use:
These functions are useful when scheduling tasks or analyzing date-related data. For instance, calculating an expiration date:
SELECT DATE_ADD(subscription_start, INTERVAL 1 YEAR) AS expiration_date
FROM subscriptions;
This query adds one year to the subscription start date to calculate the expiration date.
DATE_DIFF() / TIMESTAMP_DIFF()
- Explanation: These functions compute the difference between two dates or timestamps.
- DATE_DIFF(end_date, start_date, unit): Returns the difference between two dates in terms of the specified unit (e.g., days, months, years).
- TIMESTAMP_DIFF(end_timestamp, start_timestamp, unit): Returns the difference between two timestamps in terms of the specified unit (e.g., minutes, hours, days).
- Usage:
- DATE_DIFF(end_date, start_date, unit): Calculates the difference between two dates.
- TIMESTAMP_DIFF(end_timestamp, start_timestamp, unit): Calculates the difference between two timestamps.
- Example:
SELECT DATE_DIFF('2024-12-31', '2024-09-21', DAY) AS days_until_end_of_year;
This query calculates the number of days between today and the end of the year, which would return 101.
SELECT TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), '2024-09-20 14:00:00', HOUR) AS hours_since_event;
This query calculates the number of hours since a specific event timestamp.
- Practical Use:
These functions are useful for determining elapsed time between events or for calculating time intervals. For instance:
SELECT DATE_DIFF(order_date, delivery_date, DAY) AS delivery_duration
FROM orders;
This query calculates the number of days between order and delivery dates for each order.
EXTRACT()
- Explanation: The EXTRACT() function extracts specific parts (such as year, month, day, or hour) from a date, time, or timestamp. This function is useful when you want to analyze or group data based on specific time periods.
- Usage:
- EXTRACT(part FROM date_or_timestamp): Extracts the specified part (e.g., year, month, day, hour) from a date or timestamp.
- Example:
SELECT EXTRACT(YEAR FROM order_date) AS order_year, EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;
This query extracts the year and month from the order_date column, which is useful for time-based analysis or grouping.
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP()) AS current_hour;
This query extracts the current hour from the current timestamp.
- Available Parts:
- YEAR: Extracts the year from the date.
- MONTH: Extracts the month.
- DAY: Extracts the day.
- HOUR: Extracts the hour from a timestamp.
- MINUTE: Extracts the minute.
- SECOND: Extracts the second.
- Practical Use:
These functions are particularly useful for breaking down and analyzing data based on specific time components. For instance, grouping sales by month:
SELECT EXTRACT(MONTH FROM order_date) AS order_month, COUNT(*) AS orders_count
FROM orders
GROUP BY order_month;
This query groups the number of orders by month.
Summary of Use Cases
Date and time functions are critical when working with temporal data in BigQuery:
- CURRENT_DATE() and CURRENT_TIMESTAMP() are used to get the current date or time for comparison.
- DATE_ADD() and TIMESTAMP_ADD() help in scheduling or determining future dates.
- DATE_DIFF() and TIMESTAMP_DIFF() are used to measure time intervals between two events.
- EXTRACT() helps analyze and break down data based on specific time components (year, month, hour).
These functions enable powerful time-based analysis and operations on datasets with date or timestamp columns.
