BigQuery Timestamp to Date Conversion: Data Transformation

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!

Overview of Google BigQuery

Google BigQuery is a Cloud-based Data Warehouse that provides a Big Data Analytic Web Service for processing petabytes of data. It is intended for analyzing data on a large scale. 

It consists of two distinct components: Storage and Query Processing. It employs the Dremel Query Engine to process queries and is built on the Colossus File System for storage. These two components are decoupled and can be scaled independently and on-demand.

Google BigQuery is fully managed by Cloud service providers. We don’t need to deploy any resources, such as discs or virtual machines. It is designed to process read-only data.

BigQuery Timestamp to Date Functions in Standard SQL

Following are some of the most commonly used BigQuery Timestamp to Date Functions in Standard SQL:

• CURRENT_TIMESTAMP
• EXTRACT
• STRING
• TIMESTAMP

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 of CURRENT-TIMESTAMP

CURRENT_TIMESTAMP()

Example of CURRENT-TIMESTAMP

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      |
 *--------------------------------+-------------------*/

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 of EXTRACT

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 of EXTRACT

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                 |
 *-------------+--------------------*/

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 of STRING

STRING(timestamp_expression[, timezone])

Example of STRING

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 of TIMESTAMP

TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])
TIMESTAMP(datetime_expression[, timezone])

Examples of TIMESTAMP

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 |
 *-------------------------*/

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 of CURRENT_DATE

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 of Converting using CAST

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 of Converting from STRING using PARSE

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 of Converting with Extract()

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.

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

1. 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.
2. 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.
3. 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,

• BigQuery TRUNCATE TABLE Command Made Easy
• Google BigQuery COUNT IF and COUNT Functions: A Comprehensive Guide 101
• BigQuery String Functions 101: Syntax and Usage Simplified
• DATE_ADD function in BigQuery – Syntax and Queries

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.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions