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:
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.
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
- https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions
Manisha Jena is a data analyst with over three years of experience in the data industry and is well-versed with advanced data tools such as Snowflake, Looker Studio, and Google BigQuery. She is an alumna of NIT Rourkela and excels in extracting critical insights from complex databases and enhancing data visualization through comprehensive dashboards. Manisha has authored over a hundred articles on diverse topics related to data engineering, and loves breaking down complex topics to help data practitioners solve their doubts related to data engineering.