When working with data in BigQuery, you may encounter timestamp fields that you want to convert to simple date values instead. Timestamps in BigQuery are represented as strings in the YYYY-MM-DD HH:MM:SS format, capturing not just the date but also the time down to the second. However, for many analysis use cases you may only care about the date itself – the year, month, and day the event occurred on. Converting timestamp strings into date values allows you to group and aggregate your data by day, month, or year more easily.
In this article, we’ll explore different built-in BigQuery functionality and options for converting full timestamp strings into simple date values. Specifically, we’ll look into using the CAST
, PARSE_DATE
and STRING
functions to extract just the date out of a timestamp string. Let’s dive in!
BigQuery Timestamp to Date Functions in Standard SQL
Following are some of the most commonly used BigQuery Timestamp to Date Functions in Standard SQL:
Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.
Start for free now!
Get Started with Hevo for Free
1) CURRENT_TIMESTAMP
This BigQuery timestamp to date function produces a continuous, non-ambiguous timestamp that has exactly 60 seconds per minute. Also, the Timestamp Function does not repeat values over the leap second.
Syntax
CURRENT_TIMESTAMP()
Example
WITH t AS (SELECT 'column value' AS `current_timestamp`)
SELECT current_timestamp() AS now, t.current_timestamp FROM t;
/*--------------------------------+-------------------*
| now | current_timestamp |
+--------------------------------+-------------------+
| 2020-06-02 23:57:12.120174 UTC | column value |
*--------------------------------+-------------------*/
- The provided SQL code uses a Common Table Expression (CTE) named
t
.
- Inside the CTE, there is a column
current_timestamp
set to the static value 'column value'
.
- In the main
SELECT
statement:
current_timestamp()
retrieves the current date and time when the query is executed.
t.current_timestamp
refers to the static value 'column value'
from the CTE t
.
- The output will show:
- A column
current_timestamp
displaying the static string ‘column value’.
- A column
now
containing the current timestamp.
2) EXTRACT
You can return a specific part from the supplied Timestamp expression with this command. It supports an optional timezone parameter if you don’t want to use the default time zone.
SYNTAX
EXTRACT(part FROM timestamp_expression[AT TIME ZONE timezone])
You can extract the following PARTS of date and time with this function:
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- DAY OF WEEK
- DAY
- DAY OF YEAR
- WEEK
- WEEK(<WEEKDAY>)
- ISO WEEK
- MONTH
- QUARTER
- YEAR
- ISO YEAR
- DATETIME
- TIME
The return data type is INT64 except when the return data type is DATE, DATETIME, and TIME.
Example
WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input
/*-------------+--------------------*
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25 | 24 |
*-------------+--------------------*/
- The code defines a timestamp (
"2008-12-25 05:30:00+00"
) in a CTE named Input
.
- The
SELECT
statement uses the EXTRACT
function to retrieve the day from this timestamp in two time zones:
the_day_utc
: Day in UTC, which is 25.
the_day_california
: Day in America/Los_Angeles, which is 24.
- Because California is behind UTC, the date is one day earlier in the output.
3) STRING
This BigQuery timestamp to date function converts a Timestamp expression to a String data type. If you don’t want the default timezone, specify a different one.
Syntax
STRING(timestamp_expression[, timezone])
Example
SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;
/*-------------------------------*
| string |
+-------------------------------+
| 2008-12-25 15:30:00+00 |
*-------------------------------*/
4) TIMESTAMP
There are three parameters in this function.
- The first parameter converts a String expression to a Timestamp data type, and you can specify a time zone. string_expression[, timezone]
- The second parameter converts a Date object to a Timestamp data type. date_expression[, timezone]
- The third parameter converts a DateTime object to a Timestamp data type. datetime_expression[, timezone]
Syntax
TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])
TIMESTAMP(datetime_expression[, timezone])
Examples
SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_str |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_str |
+-------------------------+
| 2008-12-25 23:30:00 UTC |
*-------------------------*/
- The query is created
timestamp_str
with the timestamp "2008-12-25 15:30:00"
in the America/Los_Angeles time zone.
- It converts this time to UTC, which is 8 hours ahead of Los Angeles time.
- The output will display 23:30:00 UTC.
- Results may vary depending on the environment and time zone settings.
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_str |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
Date Functions in Standard SQL
There are the following DATE functions in standard SQL that BigQuery supports:
1) CURRENT_DATE
You can use this BigQuery timestamp to date function to find the Current Date in the specific or default timezone. If you don’t want to use the default time zone, use the time_zone parameter.
Syntax
CURRENT_DATE([time_zone])
2) EXTRACT
You can use this function to return a specific date part. The parts can be DAYOFWEEK, DAY, DAYOFYEAR, WEEK, WEEK(<WEEKDAY>), MONTH, QUARTER, YEAR, ISO YEAR, and ISOWEEK.
EXTRACT(part FROM date_expression)
You can also refer to this article to gain a deeper understanding of BigQuery Date Functions.
BigQuery Timestamp to Date Functions
There are four BigQuery Timestamp to Date Functions. They are as follows:
- Date type: Denotes a calendar date, and the following information is included: Year, Month, and Date: YYYY-MM-DD. This data type doesn’t include any information on time zone (e.g., 2021-07-15).
- Time type: Shows time similar to a digital watch; it is not date-dependent. The format is: HH:MM: SS (e.g., 17:35:14)
- Datetime type: Includes both the calendar time and date. However, it does not keep track of time zones. The format is YYYY-MM-DD HH:MM: SS (e.g., 2021-07-15 17:35:14).
- Timestamp type: Includes all three: Date, Time, and Time Zone information. By default, the timezone is set to UTC. You can also specify a time zone. The format is: YYYY-MM-DD [Timezone] HH:MM:SS (e.g. 2021-07-15 17:35:14 UTC).
Converting a BigQuery timestamp to date enables the transformation of an absolute point in time into a more human-readable format, providing insights into the corresponding civil date and facilitating analytical processes. You can perform the following functions on the above date and time function groups:
- Find current DateTime
- Change format
- Add and subtract your date/time
- Subtract date/time
- Group date/time
- Extract specific parts
- Calculate the difference between two dates/times
How to find current datetime?
In BigQuery, to get current date or time expression, you can use the Current function.
CURRENT_DATE()
CURRENT_DATETIME()
CURRENT_TIMESTAMP()
CURRENT_TIME()
How to change datetime format?
In BigQuery, to change the format of dates and time data, you should use FORMAT function.
FORMAT_DATE(format_string,date)
FORMAT_DATETIME(format_string, datetime)
FORMAT_TIMESTAMP(format_string, timestamp,[timezone]) FORMAT_TIME(format_string, time)
How to add and subtract datetime?
In BigQuery, to add to a date and time use the following syntax:
DATE_ADD(date_expression, INTERVAL int64_expression date_part)
DATETIME_ADD(datetime_expression, INTERVAL int64_expression date_part)
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
TIME_ADD(time_expression, INTERVAL int64_expression date_part)
In BigQuery, to subtract to a date and time, use the following syntax:
DATE_SUB(date_expression, INTERVAL int64_expression part)
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression part)
TIME_SUB(time_expression, INTERVAL int64_expression part)
The date_part values supported for addition and subtraction are:
- DAY
- HOUR
- MINUTE
- SECOND
- MILLISECOND
- MICROSECOND
- WEEK
- QUARTER
- MONTH
- YEAR
How to group date and time?
To Group date/time in BigQuery use the following syntax:
DATE_TRUNC(date_expression, date_part)
DATETIME_TRUNC(datetime_expression, date_part)
TIMESTAMP_TRUNC(timestamp_expression, date_part)
TIME_TRUNC(time_expression, date_ part)
The date_part values supported are:
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- DAYOFWEEK
- DAY
- DAYOFYEAR
- WEEK
- WEEK(<WEEKDAY>)
- ISOWEEK
- MONTH
- QUARTER
- YEAR
- ISOYEAR
How to extract date and time parts?
To perform arithmetic operations, you need to extract the date part from your datetime value. The following syntax are used in extraction,
EXTRACT(part FROM date_expression)
EXTRACT(part FROM datetime_expression)
EXTRACT(part FROM timestamp_expression)
EXTRACT(part FROM time_expression)
The date part values supported include:
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- DAYOFWEEK
- DAY
- DAYOFYEAR
- WEEK
- WEEK(<WEEKDAY>)
- ISOWEEK
- MONTH
- QUARTER
- YEAR
- ISOYEAR
How to calculate the difference between times and dates?
In BigQuery, to calculate the difference between dates and times, use the following syntax;
DATE_DIFF(date_expression_a, date_expression_b, part)
DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)
TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, part)
TIME_DIFF(time_expression_a,time_expression_b, part)
Convert Timestamp to Date Data Type in Google BigQuery
Now that you have a basic idea of BigQuery Timestamp to Date Functions, you are ready to learn about how to convert Timestamp to Date data type in Google BigQuery. In general, you can stick with TIMESTAMPs if you want to work with time zones. However, DATETIME is the most flexible datatype since you can use both date and time functionality.
Converting using CAST
The CAST
function in SQL converts one data type to another. You can convert your STRING to one of the date data types. To do this, ensure that your STRING is in the following formats:
- DATE: YYYY-MM-DD
- TIME: HH:MM: SS
- DATETIME: YYYY-MM-DD HH:MM: SS
- TIMESTAMP: YYYY-MM-DD HH:MM: SS [timezone]
Example
SELECT CAST(“2020-12-01 12:00:00” AS DATE);
The result will be 2020-12-01
Converting from STRING using PARSE
To use one of the PARSE
functions, you can format STRING in different ways; you’ll just tell the function how it should read it. There is a PARSE
function for each Date/Time Data type:
- DATE: PARSE_DATE(format_string, date_string)
- DATETIME: PARSE_DATETIME(format_string, datetime_string)
- TIMESTAMP: PARSE_TIMESTAMP(format_string, timestamp_string[, timezone])
- TIME: PARSE_TIME(format_string, time_string)
Example
SELECT PARSE_DATETIME(“%Y-%m-%d %H:%M:%S”, ‘2020-12-01 12:00:00’ )
The result will be 2020-12-01 12:00:00
Converting with Extract()
The EXTRACT()
function in SQL provides access to temporal data types—Date, Timestamp, Interval, and time. You can pull a specific date/time format out of timestamp with this command. For example, you can extract just the month from the date 2021-07-15. The output will only be July (07).
Extract field | Meaning |
YEAR | Year |
MONTH | Month |
DAY | Day of month |
HOUR | 24 hour |
MINUTE | Minute |
SECOND | Seconds (including fractions) |
TIMEZONE_HOUR | Time zone hour |
TIMEZONE_MINUTE | Time zone minute |
Example
SELECT EXTRACT(DATE FROM “2020-12-01 12:00:00”);
The result will be 2020-12-01
BigQuery Timestamp Function List
Name | Summary |
---|
CURRENT_TIMESTAMP | Returns the current date and time as a TIMESTAMP object. |
EXTRACT | Extracts part of a TIMESTAMP value. |
FORMAT_TIMESTAMP | Formats a TIMESTAMP value according to the specified format string. |
PARSE_TIMESTAMP | Converts a STRING value to a TIMESTAMP value. |
STRING | Converts a TIMESTAMP value to a STRING value. |
TIMESTAMP | Constructs a TIMESTAMP value. |
TIMESTAMP_ADD | Adds a specified time interval to a TIMESTAMP value. |
TIMESTAMP_DIFF | Gets the number of intervals between two TIMESTAMP values. |
TIMESTAMP_MICROS | Converts the number of microseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP. |
TIMESTAMP_MILLIS | Converts the number of milliseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP. |
TIMESTAMP_SECONDS | Converts the number of seconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP. |
TIMESTAMP_SUB | Subtracts a specified time interval from a TIMESTAMP value. |
TIMESTAMP_TRUNC | Truncates a TIMESTAMP value. |
UNIX_MICROS | Converts a TIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC. |
UNIX_MILLIS | Converts a TIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC. |
UNIX_SECONDS | Converts a TIMESTAMP value to the number of seconds since 1970-01-01 00:00:00 UTC. |
Table Source
BigQuery Timestamp vs. Date Functions
Google BigQuery has two features for working with time and date, namely the Timestamp and Date Functions. While the Timestamp Function represents a point in time, including both date and time components, the Date Function, on the other hand, represents only a date without any time component.
Choosing between the two functions depends on the scenario and use case. For example, if a retail company stores sales data with timestamps, using timestamp functions allows you to calculate the total sales for each month. This allows you to identify patterns and trends in monthly sales without being concerned about specific days or hours.
However, when dealing with birthdates, you may want to calculate individuals’ ages. Date functions make it straightforward to subtract the birthdate from the current date, giving you the age without considering the time component.
Importance of BigQuery Timestamp and Date Functions
- For Calculating Dates and Times: Utilizing BigQuery timestamp and date functions enables the computation of dates and times. For instance, the
DATE()
function facilitates the calculation of the date component of a timestamp, while the TIME()
function allows for the determination of the time component of a timestamp. This proves to be a valuable option for monitoring the hours allocated to particular tasks. If assessing the individual effectiveness of your team is your goal, leveraging these functions empowers you to identify potential challenges your staff might be facing.
- For Comparing Dates and Times: Leveraging BigQuery timestamp and date functions facilitates the comparison of dates and times. For instance, the
DATETIME()
function allows you to compare two timestamps, while the FORMAT_DATETIME()
function aids in formatting a timestamp for effective comparison.
- Formatting Dates and Times: Bigquery timestamp and date functions can also be utilized to format dates and times. For example, the
FORMAT_DATETIME()
function can be used to format a timestamp for comparison or display. With this function, you can present your data in an organized manner.
Conclusion
With Google BigQuery, queries take seconds to execute. Analysts throughout the companies can then use this efficiency to make decisions based on faster insights, build visualization reports on aggregate data, and forecast trends based on historical data more accurately. This article introduced you to Google BigQuery Timestamp to Date Functions and how to convert Timestamp to Date using the BigQuery Timestamp to Date Functions.
Here are a few essentials to take a deeper dive into other commands and functions of Google BigQuery,
With your Data Warehouse, Google BigQuery live and running, you’ll need to extract data from multiple platforms to conduct your analysis. However, integrating and analyzing your data from a diverse set of data sources can be challenging and this is where Hevo Data comes into the picture.
Visit our Website to Explore Hevo
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable Hevo Pricing that will help you choose the right plan for your business needs!
Share your experience of learning about BigQuery Timestamp to Date Functions. Let us know in the comments section below!
References
Osheen is a seasoned technical writer with over a decade of experience in the data industry. She specializes in writing about B2B, technology, finance, and SaaS domains. Her passion for simplifying intricate technical concepts has established her as a respected expert in the field, making her an invaluable resource for those looking to deepen their understanding of data science.