20,000 Google BigQuery Timestamp Format Examples

You will love this blog because date, timestamp functions and formatting differ dramatically between databases, and Google BigQuery handles them uniquely, but I have covered everything you need to know about formatting with brilliant examples and clear and concise explanations.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

Below are some great examples for formatting a Google BigQuery timestamp to get you a great start to understanding how BigQuery formats.

%Y: Four-digit year.
%y: Two-digit year.
%g: Two-digit year without century (00-99).
%m: Month (01-12).
%d: Day of the month (01-31) with leading zero.
%e: Day of the month (1-31) without leading zero.
%H: Hour (00-23).
%M: Minute (00-59).
%S: Second (00-59).
%E*S: Second and fractional seconds with microsecond (00.000000 – 59.999999).
%B: Full month name (e.g., January, February).
%b: Abbreviated month name (e.g., Jan, Feb).
%e: Day of the month (1-31) without leading zero.
Quarter: Literal text ‘Quarter’.
%Q: Quarter of the year (1-4), derived from the month.
%A: Full day of the week name (e.g., Sunday, Monday).
%a: Abbreviated day of the week name (e.g., Sun, Mon).
DOY: Literal text ‘DOY’.
%j: Day of the year (001-366).

The BigQuery query example below uses the FORMAT_DATE function in the WHERE and AND clause to only return rows where the customer_timestamp has a minute of ’54’ and a second of ’34’. Notice that we need to put the values in single quotes.

In the table below you can scroll to see 20,000 different BigQuery timestamp format examples.