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!

Timestamp Functions in Standard SQL

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

1) CURRENT_TIMESTAMP

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

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

3) STRING

You can use this function to convert a Timestamp expression to a String data type. You can specify a different timezone if you don’t want the default 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 |
 *-------------------------*/
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 |
 *-------------------------*/
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Learn the best practices and considerations for setting up high-performance ETL to BigQuery

Date Functions in Standard SQL

There are the following DATE functions in standard SQL that BigQuery supports:

1) CURRENT_DATE

You can use this 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.

Simplify BigQuery ETL and Analysis with Hevo’s No-code Data Pipeline

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

BigQuery Timestamp to Date Functions

BigQuery Timestamp to Date Functions
Image Source

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).

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

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 datatypes since you can take advantage of 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 fieldMeaning
YEARYear
MONTHMonth
DAYDay of month
HOUR24 hour
MINUTEMinute
SECONDSeconds (including fractions)
TIMEZONE_HOURTime zone hour
TIMEZONE_MINUTETime zone minute

Example

SELECT EXTRACT(DATE FROM “2020-12-01 12:00:00”);

The result will be 2020-12-01

BigQuery Timestamp Function List

NameSummary
CURRENT_TIMESTAMPReturns the current date and time as a TIMESTAMP object.
EXTRACTExtracts part of a TIMESTAMP value.
FORMAT_TIMESTAMPFormats a TIMESTAMP value according to the specified format string.
PARSE_TIMESTAMPConverts a STRING value to a TIMESTAMP value.
STRINGConverts a TIMESTAMP value to a STRING value.
TIMESTAMPConstructs a TIMESTAMP value.
TIMESTAMP_ADDAdds a specified time interval to a TIMESTAMP value.
TIMESTAMP_DIFFGets the number of intervals between two TIMESTAMP values.
TIMESTAMP_MICROSConverts the number of microseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP.
TIMESTAMP_MILLISConverts the number of milliseconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP.
TIMESTAMP_SECONDSConverts the number of seconds since 1970-01-01 00:00:00 UTC to a TIMESTAMP.
TIMESTAMP_SUBSubtracts a specified time interval from a TIMESTAMP value.
TIMESTAMP_TRUNCTruncates a TIMESTAMP value.
UNIX_MICROSConverts a TIMESTAMP value to the number of microseconds since 1970-01-01 00:00:00 UTC.
UNIX_MILLISConverts a TIMESTAMP value to the number of milliseconds since 1970-01-01 00:00:00 UTC.
UNIX_SECONDSConverts 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 to work with time and date, namely 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, you can 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 the age of individuals. Date functions make it straightforward to subtract the birthdate from the current date, giving you the age without the need to consider 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

Queries take seconds to get executed with Google BigQuery. Analysts throughout the companies can then take advantage of 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 carry out 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 Jain
Freelance Technical Content Writer, Hevo Data

Osheen has extensive experience in freelance writing within the data industry and is passionate about simplifying the complexities of data integration and data analysis through informative content for those delving deeper into these subjects.

No-code Data Pipeline for Google BigQuery