Data Warehousing architectures have rapidly changed over the years, and most of the notable service providers are now Cloud-based. Therefore, companies are increasingly moving to align with such offerings on the Cloud. 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. The guide also illustrates BigQuery timestamp examples to give you a clear picture of its application.

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:

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

Are you looking for a solution to centralize your data? Hevo’s fully managed No-code Data Pipeline platform helps you integrate and load data from 150+ different sources (including 40+ free sources) to a Data Warehouse such as Google BigQuery or Destination of your choice in real-time.

Get Started with Hevo for Free

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.

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

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_MICROS

A) Syntax

TIMESTAMP_MICROS(int64_expression)

B) Description

Interprets int64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC and returns a timestamp.

C) Return Data Type

TIMESTAMP

D) Example

SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;

— Display of results may differ, depending upon the environment and time zone where this query was executed.

/*————————-*

 | timestamp_value         |

 +————————-+

 | 2008-12-25 15:30:00 UTC |

 *————————-*/

8) TIMESTAMP_MILLIS

A) Sytax

TIMESTAMP_MILLIS(int64_expression)

B) Description

Interprets int64_expression as the number of milliseconds since 1970-01-01 00:00:00 UTC and returns a timestamp.

C) Return Data Type

TIMESTAMP

D) Example

SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value;

— Display of results may differ, depending upon the environment and time zone where this query was executed.

/*————————-*

 | timestamp_value         |

 +————————-+

 | 2008-12-25 15:30:00 UTC |

 *————————-*/

9) TIMESTAMP_SECONDS

A) Syntax

TIMESTAMP_SECONDS(int64_expression)

B) Description

Interprets int64_expression as the number of seconds since 1970-01-01 00:00:00 UTC and returns a timestamp.

C) Return Data Type

TIMESTAMP

D) Example

SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value;

— Display of results may differ, depending upon the environment and time zone where this query was executed.

/*————————-*

 | timestamp_value         |

 +————————-+

 | 2008-12-25 15:30:00 UTC |

 *————————-*/

10) 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             |
+---------------+

11) 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           |
+-------------------------+----------------+

12) 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    |
+-------------+

13) 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 BigQuery format 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 |
+-------------------------+

Before wrapping up, let’s cover some basics.

How Timezones Work With Timestamp Function

A timestamp indicates an absolute point in time and is independent of any time zone. But while displaying, the timestamp is usually converted to a human-readable format consisting of a civil date and time (YYYY-MM-DD HH:MM:SS) and a time zone. This is only a human-understandable way to describe the point in time that the timestamp represents and not the internal representation of the TIMESTAMP. 

Some timestamp functions have a time zone argument. A time zone is required for conversion between civil time (YYYY-MM-DD HH:MM:SS) and the absolute time represented by a timestamp. A function like PARSE_TIMESTAMP, which represents civil time, takes an input string and returns a timestamp that represents an absolute time. A time zone is needed for this conversion. EXTRACT function takes an input timestamp (absolute time) and converts it to civil time in order to extract a part of that civil time. This conversion requires a time zone. The default time zone, UTC, is used if no time zone is specified.

Some date and timestamp functions allow you to override the default time zone and specify a different one. You can specify a time zone by either supplying the time zone name (for example, America/Los_Angeles) or the time zone offset from 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. As you master the nuances of BigQuery, you should also understand best ways to centralize data to BigQuery.

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.

Visit our Website to Explore Hevo

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.

References

  1. https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions
mm
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.

No-code Data Pipeline for Google BigQuery

Try for free