Timestamps for BigQuery. Syntax and instance query simplification 101

Manisha Jena about Data Warehousing, Google BigQuery- September 15, 2021 -Write for Hevo

Going into data science and deciding to use a tool to solve a particular problem can sometimes be challenging, especially when you have a large number of options. In this era of data transformation, where organizations are constantly looking for ways to improve their day-to-day data processing and find ways to minimize the cost of these operations, handling these data transformations in the cloud has become a top priority because it is easier to manage and more cost effective.

Data warehouse architectures have changed rapidly over the years, and most reputable service providers are now cloud-based. As a result, companies are increasingly aligned with such offerings in the cloud because it provides them with lower upfront costs, enhanced scalability, and performance relative to traditional internal data warehouse systems. Google BigQuery is one of the well-known and widely accepted cloud-based data warehouse applications.

In this article, you’ll get information about the BigQuery timestamp functionality. You’ll also get a thorough understanding of Google BigQuery, its key features, date and time features, and BigQuery’s data types. Read on to learn more about the BigQuery timestamp functionality.

directory

  • Introduction of the Google BigQuery
    • Key features of Google BigQuery
  • Learn about the date and time features in Google BigQuery
  • Date and time data types in BigQuery
  • A timestamp function in standard SQL
    • BigQuery timestamp function. CURRENT_TIMESTAMP function
    • BigQuery timestamp function. The EXTRACT function
    • BigQuery timestamp function. The STRING functions
    • Big data timestamp function. Timestamp function
    • BigQuery timestamp function. Function: TIMESTAMP_ADD
    • Timestamps for BigQuery. Timestamp function: TIMESTAMP_SUB
    • BigQuery timestamp function. Big data timestamp function: TIMESTAMP_DIFF
    • BigQuery timestamp function. Function: TIMESTAMP_TRUNC
    • BigQuery timestamp function. FORMAT_TIMESTAMP function
    • BigQuery timestamp function. PARSE_TIMESTAMP function
  • conclusion

Introduction to Google BigQuery

photo

Google BigQuery is a cloud-based data warehouse that provides a big data analytics network service to process petabytes of data. Its purpose is to analyze data on a large scale. It’s made up of two different parts. Storage and query processing. It uses Dremel query engine to process queries and is stored on the Colossus file system. The two components are decoupled and can be expanded independently and on demand.

Google BigQuery is fully managed by cloud service providers. We don’t need to deploy any resources, such as disks or virtual machines. It is designed to handle read-only data. Dremel and Google BigQuery use columnar storage for fast data scanning and ANSI SQL to execute a tree structure of queries and aggregate the results across a large cluster of computers. In addition, due 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, check out the official documentation.

Key features of Google BigQuery

photo

  • Scalability. Google BigQuery provides true scalability and consistent performance with its massively parallel computing and secure storage engine.
  • Data entry format. Google BigQuery allows users to load data in various formats, such as AVRO, CSV, JSON, and more.
  • Built-in ARTIFICIAL intelligence and ML: It supports predictive analysis using its automatic ML table functionality, a code-free interface that helps develop models with optimal accuracy. Google BigQuery ML is another feature that supports k-method, logistic regression, and other algorithms.
  • Parallel processing. It uses a cloud-based parallel query processing engine to read data from thousands of disks simultaneously.

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

Simplify ETL and analysis for BigQuery with Hevo’s code-free data pipeline

A fully managed code-free Data pipeline platform like Hevo Data helps you integrate and load Data from over 100 different sources (including over 30 free sources) into a Data warehouse, such as Google BigQuery or a destination of your choice, in an effortless manner. With its minimal learning curve, Hevo can be set up in just a few minutes, allowing users to load data without impacting performance. Its powerful integration with many data sources enables users to smoothly introduce different kinds of data without having to write a line of code.

Start using Hevo for free

Check out some of Hevo’s cool features.

  • Fully automated. The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Transformation. Hevo provides preloaded transformations through Python code. It also allows you to run conversion code for each event in the data pipeline you set up. You need to edit the properties of the event object received in the transform method as parameters to perform the transformation. Hevo also provides drag-and-drop transformations such as date and control functions, JSON, and event handling, to name a few. These can be configured and tested before being put into use.
  • The connector. Hevo supports more than 100 integrations with SaaS platforms, documentation, databases, analytics and BI tools. It supports a variety of destinations, including Google BigQuery, Amazon Redshift, and Snowflake data warehouses; Amazon S3 Data Lake; MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL, etc.
  • Real-time data transmission. Hevo provides real-time data migration, so you can always have data available for analysis.
  • 100% complete and accurate data transmission. Hevo’s robust infrastructure ensures reliable data transmission and zero data loss.
  • Scalable infrastructure. Hevo has built-in integration of over 100 sources ** (including over 30 free sources) ** to help you scale your data infrastructure as needed.
  • 24/7 on-site support. The Hevo team provides exceptional support 24/7 via chat, email and support calls.
  • Mode management. H EVO eliminates the tedious task of schema management, automatically detecting the schema of incoming data and mapping it to the target schema.
  • Real-time monitoring. Hevo allows you to monitor data streams, so you can check where your data is at a particular point in time.

Sign up here to get a 14-day free trial!

Learn about the date and time features in Google BigQuery

BigQuery supports four sets of date and time functions. DATE, TIME, DATETIME and TIMESTAMP. These collections include more specific functions such as CURRENT_DATETIME, DATE_SUB, EXTRACT, FORMAT_TIME, and so on. These functions allow BigQuery users to work with date and time data types. For example, they can extract part of a date or time expression, add an interval for 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 represents a calendar date, regardless of time zone. The information includes: Year, month, and date: YYYY-MM-DD (for example, 2021-05-15).
  • Time type. It shows the time in the same way as a digital watch; It doesn’t depend on the date. The format is. HH:MM:SS, for example, 16:45:14.
  • Date time type. It includes calendar dates and times. It doesn’t track time zones. The format is. Yyyy-mm-dd HH:MM:SS (for example, 2021-05-15 16:45:23).
  • Timestamp type. Date, time, and time zone information are included in the timestamp. If no time zone is given, the format falls back to UTC. The format is. Yyyy-mm-dd [Time zone] HH:MM:SS (for example, 2021-05-15 16:45:18 UTC).

A timestamp function in standard SQL

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

Some of BigQuery’s timestamp functions are as follows.

  • BigQuery timestamp function. CURRENT_TIMESTAMP function
  • BigQuery timestamp function. The EXTRACT function
  • Big data timestamp function. The STRING functions
  • Big data timestamp function. Timestamp function
  • BigQuery timestamp function. Function: TIMESTAMP_ADD
  • Timestamps for BigQuery. Timestamp function: TIMESTAMP_SUB
  • BigQuery timestamp function. Big data timestamp function: TIMESTAMP_DIFF
  • BigQuery timestamp function. Function: TIMESTAMP_TRUNC
  • BigQuery timestamp function. FORMAT_TIMESTAMP function
  • BigQuery timestamp function. PARSE_TIMESTAMP function

1) CURRENT_TIMESTAMP function

A) grammar

CURRENT_TIMESTAMP()
Copy the code

B) describe

**CURRENT TIMESTAMP()** Returns a TIMESTAMP value, which is continuous and unambiguous, with 60 seconds per minute and does not repeat the value on leap seconds. In this BigQuery Timestamp function, the use of parentheses is optional.

The BigQuery Timestamp function handles leap seconds by spreading them out over a 20-hour time range based on the leap seconds inserted.

C)Supported input types

Do not apply

D) Result data type

TIMESTAMP data type

E) sample

SELECT CURRENT_TIMESTAMP() as now;
Copy the code

The output.

+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | now | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 2020-06-02 23:57:12. 120174 UTC | +--------------------------------+Copy the code

When a list with the name current_TIMESTAMP is present, the column name and function call without parentheses are ambiguous. To ensure a function call, you can add parentheses. To ensure that the column name is correct, qualify it with its scope variable. For example, the following query selects functions in the NOW column and table columns 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
Copy the code

The output.

+--------------------------------+-------------------+ | now | current_timestamp | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 2020-06-02 23:57:12. 120174 UTC | column value | +--------------------------------+-------------------+Copy the code

2) EXTRACT function

A) grammar

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

B)describe

The BigQuery Timestamp function returns a value corresponding to the specified part from the supplied timestamp_expression. This function supports an optional ** time zone ** argument.

Some values can be selected from the following.

  • Microseconds (MICROSECOND
  • ms
  • seconds
  • minutes
  • hours
  • Weeks of the day
  • day
  • A day of the year
  • weeks
  • Weeks ()
  • Weeks (ISOWEEK
  • month
  • In the fourth quarter
  • years
  • Years (ISOYEAR)
  • The date of
  • The date of
  • time

Low-order time periods are truncated in the return value. When extracting seconds, EXTRACT truncates the millisecond and microsecond values.

For further information on some of the values, visit here.

C) Return data type

INT64, except when.

  • Part is a DATE, so it returns a DATE object.
  • Part is DATETIME, so it returns a DATETIME object.
  • Part is TIME, so it returns a TIME object.

D) the sample

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

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;Copy the code

The output.

+-------------+--------------------+ | the_day_utc | the_day_california | +-------------+--------------------+ | 25 | 24  | +-------------+--------------------+Copy the code

Here, EXTRACT returns values corresponding to different time segments from a list 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;
Copy the code

The 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 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - +Copy the code

In the following example, timestamp_expression is on a Monday. EXTRACT calculates the first column using the week starting on Sunday, and the second column using the week starting 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 |+-------------------------+-------------+---------------+Copy the code

The 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            |
+-------------------------+-------------+---------------
Copy the code

3) STRING

A) grammar

STRING(timestamp_expression[, timezone])
Copy the code

B) describe

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

C)Return data type

STRING data type

D)The sample

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

The output.

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00+00        |
+-------------------------------+
Copy the code

4) TIMESTAMP function

A) grammar

TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])
TIMESTAMP(datetime_expression[, timezone])
Copy the code

B) describe

  • String_expression [, timezone]. It converts a STRING expression to the TIMESTAMP data type. String_expression must include a timestamp. If string_expression contains a time zone in the timestamp literal, it should not contain an explicit time zone parameter.
  • 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.

The 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)The sample

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

The 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 |
+-------------------------+
Copy the code
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;
Copy the code

The 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 |
+-------------------------+
Copy the code
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;
Copy the code

The 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 |
+-------------------------+
Copy the code
SELECT TIMESTAMP(DATETIME "2008-12-25 15:30:00") AS timestamp_datetime;
Copy the code

The 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 |
+-------------------------+
Copy the code
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;
Copy the code

The 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 | +-------------------------+Copy the code

5) TIMESTAMP_ADD function

A) grammar

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
Copy the code

B) describe

The BigQuery timestamp function adds the int64_ expression unit of the date portion to the timestamp, regardless of any time zone.

TIMESTAMP_ADD Supports the following values for the date part.

  • Microseconds (MICROSECOND
  • ms
  • seconds
  • minutes
  • Hours (60 minutes
  • A day (24 hours)

C) Return data type

TIMESTAMP data type

D)The sample

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;
Copy the code

The 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 |
+-------------------------+-------------------------+
Copy the code

6) TIMESTAMP_SUB function

A) grammar

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)
Copy the code

B) describe

The BigQuery timestamp function subtracts the date-part int64_ expression unit from the timestamp, regardless of any time zone.

TIMESTAMP_SUB supports the following date_part values.

  • Microseconds (MICROSECOND
  • ms
  • seconds
  • minutes
  • Hours (60 minutes
  • A day (24 hours)

C)Return data type

TIMESTAMP data type

D)The sample

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;

Copy the code

The 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 |
+-------------------------+-------------------------+
Copy the code

7) TIMESTAMP_DIFF function

A) grammar

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)
Copy the code

B) describe

The BigQuery Timestamp function returns the number of complete intervals between the specified date-part of two Timestamp objects ** (timestamp_expression_A – timestamp_expression_B). If the first TIMESTAMP precedes the second, the output is negative. If the result of the calculation overflows, such as the difference in microseconds between two TIMESTAMP objects overflows an INT64** value, an exception is thrown.

TIMESTAMP_DIFF supports the following values for the date part.

  • MICROSECOND
  • ms
  • seconds
  • MINUTE
  • Hours (60 minutes
  • A day (24 hours)

C)Return data type

INT64 Data type

D)The sample

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;
Copy the code

The 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 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- +Copy the code

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

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

The output.

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+
Copy the code

In this case, the result is 0, because only the entire specified HOUR interval is included.

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

Output the result.

+---------------+
| negative_diff |
+---------------+
| 0             |
+---------------+
Copy the code

8) TIMESTAMP_TRUNC function

A) grammar

TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])
Copy the code

B)describe

The BigQuery Timestamp function truncates the Timestamp to the granularity of date_part.

TIMESTAMP_TRUNC Supports the following values for the date part.

  • Microseconds (MICROSECOND
  • ms
  • seconds
  • MINUTE
  • hours
  • day
  • weeks
  • Weeks ()
  • weeks
  • month
  • In the fourth quarter
  • years
  • ISOYEAR

The TIMESTAMP_TRUNC function supports an optional time zone parameter. This parameter applies to the following date sections.

  • MINUTE
  • hours
  • day
  • weeks
  • Weeks ()
  • weeks
  • month
  • In the fourth quarter
  • years
  • ISOYEAR

For further information on date_Part values, visit here.

If you want to use a time zone other than the default UTC as part of the truncation operation, you can use these parameters.

When a TIMESTAMP is truncated to MINUTE or HOUR, TIMESTAMP_TRUNC determines the civil time of a TIMESTAMP in the specified (or default) time zone and subtracts minutes and seconds (when truncated to HOUR) or seconds (when truncated to MINUTE) from that TIMESTAMP. While this provides intuitive results in most cases, the results are not intuitive during the transition period of daylight saving time that is not measured in hours.

C) Return data type

TIMESTAMP data type

D) the sample

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;Copy the code

The output.

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

In the following example, timestamp_expression has a time zone offset of +12. The TIMEstamp_expression for UTC time is shown in the first column. The second column shows the results of TIMESTAMP_TRUNC for the week starting Monday. TIMESTAMP_TRUNC truncates the timestamp _ expression to the previous Monday because it is Sunday in UTC. The same function is shown in the third column with the optional time zone definition parameter “Pacific/Oakland”. The code here truncates timestamp_expression using New Zealand Daylight Time, which is 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);
Copy the code

The 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 |
+-------------------------+-------------------------+-------------------------+
Copy the code

In the example below, the original timestamp_expression is in Gregorian calendar 2015. However, TIMESTAMP_TRUNC with an ISOYEAR date part truncates the timestamp _ expression to the beginning of the ISO year instead of the Gregorian year. Because the first Thursday of the 2015 calendar year is 2015-01-01, the 2015 ISO year begins on the previous Monday, December 29, 2014. Therefore, the ISO year boundary before 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;
Copy the code

The 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 | +-------------------------+----------------+Copy the code

9) FORMAT_TIMESTAMP function

A) grammar

FORMAT_TIMESTAMP(format_string, timestamp[, timezone])
Copy the code

B) describe

The BigQuery Timestamp function formats a Timestamp based on the specified format_string.

C) Return data type

STRING data type

D) the sample

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;
Copy the code

The output.

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
Copy the code
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; 
Copy the code

The output.

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
Copy the code
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;Copy the code

The output.

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+
Copy the code

10) PARSE_TIMESTAMP function

A) grammar

PARSE_TIMESTAMP(format_string, timestamp_string[, timezone])
Copy the code

B) describe

The BigQuery Timestamp function converts the string representation of the Timestamp into a Timestamp object.

Format_string contains the format element that specifies the formatting of the timestamp string. Each element in the timestamp string must be paired with an element in the format_string. The position of each element in format_string must match that 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")Copy the code

The format string format in most fully support, in addition to * *, * * % Q % % % % a, a, g, g, % % j, P, % % % % u, u, V, w and w * * * * %.

There are a few things to keep in mind when using PARSE_TIMESTAMP.

  • An unspecified field. Any fields not specified are 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 any. If not, the default time zone, UTC, is used for initialization. For example, if no year is specified, the default is 1970, and so on.
  • Case insensitive name. Names, such as Monday, February, etc., are case insensitive.
  • White space. One or more consecutive whitespace in the format string matches zero or more consecutive whitespace in the timestamp string. Also, leading and trailing whitespace in the timestamp string are always allowed, even if they are not in the format string.
  • Format takes precedence. When two (or more) format elements have overlapping information (e.g. **%F and %Y both affect the year), the last format element usually takes precedence over any earlier format element, with some exceptions (see descriptions of %s**, %C, and **% Y **).

C) Return data type

TIMESTAMP data type

D) the sample

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

The output.

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

For further information on the BigQuery timestamp function, you can refer to the official documentation.

conclusion

In this article, you’ve learned about Google BigQuery, its main features, and timestamps. This article also provides information about the types of the date and type functions and the usage of the BigQuery timestamp function.

Hevo Data, a code-free Data pipeline, gives you a consistent and reliable solution to manage Data transfers between various sources and various desired destinations with just a few clicks.

Visit our website to explore Hevo

Hevo Data’s powerful integration with over 100 Data sources ** (including over 30 free Data sources) ** enables you not only to export Data from the Data sources you need and load it to a destination of your choice, such as Google BigQuery, but also to transform and enrich your Data so that it can be analyzed, This way you can focus on your key business needs and use BI tools for insightful analysis.

Sign up for a 14-day free trial to experience the feature-rich Hevo suite firsthand. You can also take a look at the amazing prices, which will help you choose the best plan that meets your requirements.

Share your understanding of BigQuery timestamp functionality in the comments section below! We’d like to hear what you think.

Code free data pipeline for Google BigQuery

A free trial

  • BigQuery timestamp function
  • Date and time data types
  • Google BigQuery
  • Standard SQL