BigQuery Timestamp Functions: Syntax & Example Queries Simplified 101

Venturing into Data Science and deciding on a tool to use to solve a given problem can be challenging at times especially when you have a wide array of choices. In this age of data transformation where organizations are constantly seeking out ways to improve the day to day handling of data being produced and looking for methods to minimize the cost of having these operations, it has become imperative to handle such data transformations in the Cloud as it is a lot easier to manage and is also cost-efficient.

Data Warehousing architectures have rapidly changed over the years and most of the notable service providers are now Cloud-based. Therefore, companies are increasingly on the move to align with such offerings on the Cloud as it provides them with a lower upfront cost, enhances scalability, and performance as opposed to traditional On-premise Data Warehousing systems. Google BigQuery is among one of the well-known and widely accepted Cloud-based Data Warehouse Applications.

In this article, you will gain information about BigQuery Timestamp Functions. You will also gain a holistic understanding of Google BigQuery, its key features, Date & Time functions, and the data types in BigQuery. Read along to find out in-depth information about BigQuery Timestamp Functions.

Table of Contents

• Introduction to Google BigQuery
  • Key Features of Google BigQuery
• Understanding Date & Time Functions in Google BigQuery
• Date and Time Data Types in BigQuery
• Timestamp Functions in Standard SQL
  • BigQuery Timestamp Function: CURRENT_TIMESTAMP Function
  • BigQuery Timestamp Function: EXTRACT Function
  • BigQuery Timestamp Function: STRING Function
  • BigQuery Timestamp Function: TIMESTAMP Function
  • BigQuery Timestamp Function: TIMESTAMP_ADD Function
  • BigQuery Timestamp Function: TIMESTAMP_SUB Function
  • BigQuery Timestamp Function: TIMESTAMP_DIFF Function
  • BigQuery Timestamp Function: TIMESTAMP_TRUNC Function
  • BigQuery Timestamp Function: FORMAT_TIMESTAMP Function
  • BigQuery Timestamp Function: PARSE_TIMESTAMP Function
• Conclusion

Introduction to 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.

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

Get Guide for Free

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. Dremel and Google BigQuery use Columnar Storage for quick data scanning, as well as a tree architecture for executing queries using ANSI SQL and aggregating results across massive computer clusters. Furthermore, owing to its short deployment cycle and on-demand pricing, Google BigQuery is serverless and designed to be extremely scalable.

For further information about Google Bigquery, follow the Official Documentation.

Key Features of Google BigQuery

• Scalability: Google BigQuery offers true scalability and consistent performance using its massively parallel computing and secure storage engine.
• Data Ingestion Formats: Google BigQuery allows users to load data in various formats such as AVRO, CSV, JSON, etc.
• Built-in AI & ML: It supports predictive analysis using its auto ML tables feature, a codeless interface that helps develop models having the best in class accuracy. Google BigQuery ML is another feature that supports algorithms such as K means, Logistic Regression, etc.
• Parallel Processing: It uses a cloud-based parallel query processing engine that reads data from thousands of disks at the same time.

For further information on Google BigQuery, you can check the official website here.

Understanding Date & Time Functions in Google BigQuery

BigQuery supports four sets of date and time functions: DATE, TIME, DATETIME, and TIMESTAMP. These sets include more specific functions like CURRENT_DATETIME, DATE_SUB, EXTRACT, FORMAT_TIME, and so on. These functions allow BigQuery users to handle Date and Time Datatypes. They can, for example, extract a portion of a date or time expression, add an interval to a date or time, and so on. 

Date and Time Data Types in BigQuery

The data types available for time and date in Google BigQuery are as follows:

• Date type: It denotes a calendar date, regardless of time zone. The following information is included: year, month, and date: YYYY-MM-DD (e.g. 2021-05-15).
• Time type: It shows time in the same way as a digital watch does; it is not date-dependent. The format is: HH:MM:SS (e.g. 16:45:14)
• Datetime type: It includes both the calendar date and the time. It does not keep track of time zones. The format is: YYYY-MM-DD HH:MM:SS (e.g. 2021-05-15 16:45:23).
• Timestamp type: Date, time, and time zone information are all included in timestamps. If no time zone is given, the format falls back to UTC. The format is: YYYY-MM-DD [Timezone] HH:MM:SS (e.g. 2021-05-15 16:45:18 UTC).

Timestamp Functions in Standard SQL

The Timestamp functions return a timestamp from a value or a pair of values. Google BigQuery supports the following BigQuery Timestamp functions.

Some of the BigQuery Timestamp Functions are as follows:

• BigQuery Timestamp Function: CURRENT_TIMESTAMP Function
• BigQuery Timestamp Function: EXTRACT Function
• BigQuery Timestamp Function: STRING Function
• BigQuery Timestamp Function: TIMESTAMP Function
• BigQuery Timestamp Function: TIMESTAMP_ADD Function
• BigQuery Timestamp Function: TIMESTAMP_SUB Function
• BigQuery Timestamp Function: TIMESTAMP_DIFF Function
• BigQuery Timestamp Function: TIMESTAMP_TRUNC Function
• BigQuery Timestamp Function: FORMAT_TIMESTAMP Function
• BigQuery Timestamp Function: PARSE_TIMESTAMP Function

1) CURRENT_TIMESTAMP function

A) Syntax

CURRENT_TIMESTAMP()

B) Description

CURRENT TIMESTAMP() returns a TIMESTAMP value that is continuous and non-ambiguous, has 60 seconds per minute and does not repeat values over the leap second. The use of parentheses in this BigQuery Timestamp function is optional.

This BigQuery Timestamp function handles leap seconds by spreading them throughout a 20-hour timeframe based on the inserted leap second.

C) Supported Input Types

Not applicable

D) Result Data Type

TIMESTAMP Data type

SELECT CURRENT_TIMESTAMP() as now;

E) Examples

Output:

+--------------------------------+
| now                            |
+--------------------------------+
| 2020-06-02 23:57:12.120174 UTC |
+--------------------------------+

When a column named current_timestamp is present, the column name and the function call without parentheses are ambiguous. To ensure the function call, you can add parentheses. To ensure the column name is correct, qualify it with its range variable. For example, the following query will select the function in the now column and the table column in the current_timestamp column.

WITH t AS (SELECT 'column value' ASWITH t AS (SELECT 'column value' AS `current_timestamp`)
SELECT current_timestamp() AS now, t.current_timestamp FROM t; `current_timestamp`)SELECT current_timestamp() AS now, t.current_timestamp FROM t

Output:

+--------------------------------+-------------------+
| now                            | current_timestamp |
+--------------------------------+-------------------+
| 2020-06-02 23:57:12.120174 UTC | column value      |
+--------------------------------+-------------------+

2) EXTRACT Function

A) Syntax

EXTRACT(part FROM timestamp_expression [AT TIME ZONE timezone])

B) Description

This BigQuery Timestamp function returns a value that corresponds to the specified part from a supplied timestamp_expression. This function supports an optional timezone parameter.

The part values are to be selected from:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK
• WEEK(<WEEKDAY>)
• ISOWEEK
• MONTH
• QUARTER
• YEAR
• ISOYEAR
• DATE
• DATETIME
• TIME

Lower order time periods are truncated in the returned values. When extracting seconds, EXTRACT truncates the millisecond and microsecond values.

For further information on part values, visit here.

C) Return Data Type

INT64, except when:

• part is DATE then it returns a DATE object.
• part is DATETIME then it returns a DATETIME object.
• part is TIMEthen it returns a TIME object.

D) Examples

Here, EXTRACT returns a value corresponding to the DAY time part.

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;

Output:

+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25          | 24                 |
+-------------+--------------------+

Here, EXTRACT returns values corresponding to different time parts from a column of timestamps.

WITH Timestamps AS (
  SELECT TIMESTAMP("2005-01-03 12:34:56+00") AS timestamp_value UNION ALL
  SELECT TIMESTAMP("2007-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-01-01 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-01-02 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-05-26 12:00:00+00")
)
SELECT
  timestamp_value,
  EXTRACT(ISOYEAR FROM timestamp_value) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp_value) AS isoweek,
  EXTRACT(YEAR FROM timestamp_value) AS year,
  EXTRACT(WEEK FROM timestamp_value) AS week
FROM Timestamps
ORDER BY timestamp_value;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+---------+---------+------+------+
| timestamp_value         | isoyear | isoweek | year | week |
+-------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56 UTC | 2005    | 1       | 2005 | 1    |
| 2007-12-31 12:00:00 UTC | 2008    | 1       | 2007 | 52   |
| 2009-01-01 12:00:00 UTC | 2009    | 1       | 2009 | 0    |
| 2009-12-31 12:00:00 UTC | 2009    | 53      | 2009 | 52   |
| 2017-01-02 12:00:00 UTC | 2017    | 1       | 2017 | 1    |
| 2017-05-26 12:00:00 UTC | 2017    | 21      | 2017 | 21   |
+-------------------------+---------+---------+------+------+

In the example given below, timestamp_expression falls on a Monday. EXTRACT calculates the first column using weeks that begin on Sunday, while the second column is calculated using weeks that begin on Monday.

WITH table AS (SELEWITH table AS (SELECT TIMESTAMP("2017-11-05 00:00:00+00") AS timestamp_value)
SELECT
  timestamp_value,
  EXTRACT(WEEK(SUNDAY) FROM timestamp_value) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp_value) AS week_monday
FROM table;CT TIMESTAMP("2017-11-05 00:00:00+00") AS timestamp_value)SELECT  timestamp_value,  EXTRACT(WEEK(SUNDAY) FROM timestamp_value) AS week_sunday,  EXTRACT(WEEK(MONDAY) FROM timestamp_value) AS week_mondayFROM table;-- Display of results may differ, depending upon the environment and time zone where this query was executed.+-------------------------+-------------+---------------+| timestamp_value         | week_sunday | week_monday   |+-------------------------+-------------+---------------+| 2017-11-05 00:00:00 UTC | 45          | 44            |+-------------------------+-------------+---------------+

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------+---------------+
| timestamp_value         | week_sunday | week_monday   |
+-------------------------+-------------+---------------+
| 2017-11-05 00:00:00 UTC | 45          | 44            |
+-------------------------+-------------+---------------

3) STRING

A) Syntax

STRING(timestamp_expression[, timezone])

B) Description

This BigQuery Timestamp function converts a timestamp_expression to a STRING data type. It also supports an optional parameter to specify a time zone.

C) Return Data Type

STRING Data type

D) Example

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;

Output:

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00+00        |
+-------------------------------+

4) TIMESTAMP Function

A) Syntax

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

B) Description

• string_expression[, timezone]: It converts a STRING expression to a TIMESTAMP data type. The string_expression must include a timestamp literal. If string_expression includes a timezone in the timestamp literal, then do explicit timezone argument shouldn’t be included.
• date_expression[, timezone]: It converts a DATE object to a TIMESTAMP data type.
• datetime_expression[, timezone]: It converts a DATETIME object to a TIMESTAMP data type.

This BigQuery Timestamp function supports an optional parameter to specify a time zone. If no time zone is specified, the default time zone, UTC, is used.

C) Return Data Type

TIMESTAMP Data type

D) Examples

SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;

Output:

-- 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;

Output:

-- 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;

Output:

-- 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(DATETIME "2008-12-25 15:30:00") AS timestamp_datetime;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_datetime      |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_date          |
+-------------------------+
| 2008-12-25 00:00:00 UTC |
+-------------------------+

5) TIMESTAMP_ADD Function

A) Syntax

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

B) Description

This BigQuery Timestamp function adds int64_expression units of date_part to the timestamp, independent of any time zone.

TIMESTAMP_ADD supports the following values for date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR (60 MINUTES)
• DAY (24 HOURS)

C) Return Data Types

TIMESTAMP Data type

D) Example

SELECT  TIMESTAMP("2008-12-25 15:30:00+00"SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later) AS original,  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+
| original                | later                   |
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:40:00 UTC |
+-------------------------+-------------------------+

6) TIMESTAMP_SUB Function

A) Syntax

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

B) Description

This BigQuery Timestamp function subtracts int64_expression units of date_part from the timestamp, independent of any time zone.

TIMESTAMP_SUB supports the following values for date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR (60 MINUTES)
• DAY (24 HOURS)

C) Return Data Type

TIMESTAMP Data type

D) Example

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+
| original                | earlier                 |
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:20:00 UTC |
+-------------------------+-------------------------+

7) TIMESTAMP_DIFF Function

A) Syntax

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)

B) Description

This BigQuery Timestamp function returns the number of complete specified date_part intervals between two TIMESTAMP objects (timestamp_expression_a – timestamp_expression_b). If the first TIMESTAMP is before the second, the output is negative. If the computation overflows the result type, such as if the difference in microseconds between the two TIMESTAMP objects would overflow an INT64 value, an exception is thrown.

TIMESTAMP_DIFF supports the following values for date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR (60 MINUTES)
• DAY (24 HOURS)

C) Return Data Type

INT64 Data type

D) Example

SELECT
  TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
  TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) 
AS hours;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+-------+
| later_timestamp         | earlier_timestamp       | hours |
+-------------------------+-------------------------+-------+
| 2010-07-07 10:20:00 UTC | 2008-12-25 15:30:00 UTC | 13410 |
+-------------------------+-------------------------+-------+

In the following example, the first timestamp occurs before the second timestamp, resulting in a negative output.

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

Output:

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

In this example, the result is 0 because only the number of whole specified HOUR intervals are included.

SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR)

Output:

+---------------+
| negative_diff |
+---------------+
| 0             |
+---------------+

8) TIMESTAMP_TRUNC Function

A) Syntax

TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])

B) Description

This BigQuery Timestamp function truncates a timestamp to the granularity of date_part.

TIMESTAMP_TRUNC supports the following values for date_part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>)
• ISOWEEK
• MONTH
• QUARTER
• YEAR
• ISOYEAR

TIMESTAMP_TRUNC function supports an optional timezone parameter. This parameter applies to the following date_parts:

• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>)
• ISOWEEK
• MONTH
• QUARTER
• YEAR
• ISOYEAR

For further information on date_part values, visit here.

These parameters can be used if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation.

When truncating a TIMESTAMP to MINUTE or HOUR, TIMESTAMP_TRUNC determines the civil time of the TIMESTAMP in the specified (or default) time zone and subtracts the minutes and seconds (when truncating to HOUR) or the seconds (when truncating to MINUTE) from that TIMESTAMP. While this provides intuitive results in most cases, the result is non-intuitive near daylight savings transitions that are not hour aligned.

C) Return Data Type

TIMESTAMP Data type

D) Examples

SELECT  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:0SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted;0+00", DAY, "UTC") AS utc,  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la; 

Output:

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+

In the following example, timestamp_expression has a time zone offset of +12. The timestamp_expression in UTC time is displayed in the first column. The second column displays the results of TIMESTAMP_TRUNC for weeks that begin on Monday. TIMESTAMP_TRUNC truncates the timestamp_expression to the prior Monday since it falls on a Sunday in UTC. The identical function is shown in the third column with the optional Time zone definition argument ‘Pacific/Auckland’. The code truncates the timestamp_expression here by using New Zealand Daylight Time, which occurs on a Monday.

SELECT
  timestamp_value AS timestamp_value,
  TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "UTC") AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "Pacific/Auckland") AS nzdt_truncated
FROM (SELECT TIMESTAMP("2017-11-06 00:00:00+12") AS timestamp_value);

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+-------------------------+
| timestamp_value         | utc_truncated           | nzdt_truncated          |
+-------------------------+-------------------------+-------------------------+
| 2017-11-05 12:00:00 UTC | 2017-10-30 00:00:00 UTC | 2017-11-05 11:00:00 UTC |
+-------------------------+-------------------------+-------------------------+

In the following example, the original timestamp_expression is in the Gregorian calendar year 2015. However, TIMESTAMP_TRUNC with the ISOYEAR date part truncates the timestamp_expression to the beginning of the ISO year, not the Gregorian calendar year. Because the first Thursday of the 2015 calendar year was 2015-01-01, the ISO year 2015 began on the Monday prior, 2014-12-29. As a result, the ISO year boundary preceding the timestamp_expression 2015-06-15 00:00:00+00 is 2014-12-29.

SELECT
  TIMESTAMP_TRUNC("2015-06-15 00:00:00+00", ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP "2015-06-15 00:00:00+00") AS isoyear_number;

Output:

-- Display of results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+----------------+
| isoyear_boundary        | isoyear_number |
+-------------------------+----------------+
| 2014-12-29 00:00:00 UTC | 2015           |
+-------------------------+----------------+

9) FORMAT_TIMESTAMP Function

A) Syntax

FORMAT_TIMESTAMP(format_string, timestamp[, timezone])

B) Description

This BigQuery Timestamp function formats a timestamp according to the specified format_string.

C) Return Data Type

STRING Data type

D) Example

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMPSELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted; "2008-12-25 15:30:00+00", "UTC") AS formatted;

Output:

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+

SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") 
 AS SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") AS formatted; 

Output:

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+

SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTASELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00+00")
  AS formatted;MP "2008-12-25 15:30:00+00")  AS formatted;

Output:

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

10) PARSE_TIMESTAMP Function

A) Syntax

PARSE_TIMESTAMP(format_string, timestamp_string[, timezone])

B) Description

This BigQuery Timestamp function converts a string representation of a timestamp to a TIMESTAMP object.

The format_string contains the format elements that specify how the timestamp_string is formatted.  Each element in the timestamp string must be paired with an element in format_string. The location of each element in format_string must match the location of each element in timestamp_string.

-- This works because elements on both sides m-- This works because elements on both sides match.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008")
-- This works because %c can find all matching elements in timestamp_string.
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008")atch.SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")-- This doesn't work because the year element is in different locations.SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")-- This doesn't work because one of the year elements is missing.SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008")-- This works because %c can find all matching elements in timestamp_string.SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008")

The format string fully supports most format elements, except for %Q, %a, %A, %g, %G, %j, %P, %u, %U, %V, %w, and %W.

When using PARSE_TIMESTAMP, the following should be kept in mind:

• Unspecified fields: Any unspecified field is initialized from 1970-01-01 00:00:00.0. This initialization value uses the time zone specified by the function’s time zone argument, if present. If not, the initialization value uses the default time zone, UTC. For instance, if the year is unspecified then it defaults to 1970, and so on.
• Case insensitive names: Names, such as Monday, February, etc, are case insensitive.
• Whitespace. One or more consecutive white spaces in the format string matches zero or more consecutive white spaces in the timestamp string. Along with that, leading and trailing white spaces in the timestamp string are always allowed, even if they are not in the format string.
• Format precedence. When two (or more) format elements have overlapping information (for example both %F and %Y affect the year), the last one generally overrides any earlier ones, with some exceptions (see the descriptions of %s, %C, and %y).

C) Return Data Type

TIMESTAMP Data type

D) Example

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

Output:

+-------------------------+
| parsed                  |
+-------------------------+
| 2008-12-25 07:30:00 UTC |
+-------------------------+

For further information about BigQuery Timestamp Functions, you can follow the Official Documentation.

Conclusion

In this article, you have learned about Google BigQuery, its key features, format and Timestamps. This article also provided information on the types of Date & Type functions and usage of BigQuery Current Timestamp functions.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Hevo Data with its strong integration with 100+ data sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice such as Google BigQuery, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools. 

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing pricing, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the BigQuery Timestamp Functions in the comment section below! We would love to hear your thoughts.

Manisha Jena Former Research Analyst, Hevo Data

Manisha is a data analyst with experience in diverse data tools like Snowflake, Google BigQuery, SQL, and Looker. She has written more than 100 articles on diverse topics related to data industry.