BigQuery Datetime and BigQuery Timestamp Functions

DATE, TIME, DATETIME, and TIMESTAMP are the four groups of date and time functions that are supported in BigQuery. These groups contain more specific functions such as CURRENT_DATETIME, DATE_SUB, EXTRACT, FORMAT_TIME, and so on. These functions let users manipulate date and time data types in BigQuery. For example, they have the ability to extract a part of a date or time expression, add an interval to a date or time, etc.

Determining when and how to utilize these functions may be tricky. To make things simpler, we’ve included examples of the many ways you may use these functions throughout this article.

Automate your analytics and reporting with Coupler.io

Get started for free

Date and Time data types in BigQuery

Before digging into the manipulation of date and time values in BigQuery, let’s recall which data types are available for time and date:

Date type: indicates a calendar date, irrespective of time zone. Year, month, and date are all included: YYYY-MM-DD (e.g. 2021-07-15).
Time type: displays time the same as a digital watch would; it is not date-dependent. Example: HH:MM:SS (e.g. 17:10:14)
Datetime type: comprises both calendar date and time. It does not store time zone information: YYYY-MM-DD HH:MM:SS (e.g. 2021-07-11 16:09:23).
Timestamp type: comprises date, time, and time zone information. If the time zone is unspecified, the format defaults to UTC: YYYY-MM-DD HH:MM:SS [timezone] (e.g. 2021-07-15 18:10:18 UTC).

More on data types and how they are categorized is explained in our detailed tutorial on BigQuery Data Types.

DATE, TIME, DATETIME, and TIMESTAMP functions in BigQuery SQL

Below is a table of the four date and time function groups in BigQuery, as well as their sub-functions.

Date Functions	Time Functions	Datetime functions	Timestamp functions
CURRENT_DATE	CURRENT_TIME	CURRENT_DATE	CURRENT_TIMESTAMP
DATE	TIME	DATETIME	TIMESTAMP
EXTRACT	EXTRACT	EXTRACT	EXTRACT
DATE_ADD	TIME_ADD	DATETIME_ADD	TIMESTAMP_ADD
DATE_SUB	TIME_SUB	DATETIME_SUB	TIMESTAMP_SUB
DATE_DIFF	TIME_DIFF	DATETIME_DIFF	TIMESTAMP_DIFF
DATE_TRUNC	TIME_TRUNC	DATETIME_TRUNC	TIMESTAMP_TRUNC
FORMAT_DATE	FORMAT_TIME	FORMAT_DATETIME	FORMAT_TIMESTAMP
PARSE_DATE	PARSE_TIME	PARSE_DATETIME	PARSE_TIMESTAMP
LAST_DAY		LAST_DAY
DATE_FROM_UNIX_DATE		DATETIME_FROM_UNIX_DATE
UNIX_DATE			UNIX_SECONDS
			STRING
			UNIX_SECONDS
			UNIX_MICROS
			TIMESTAMP_MICROS
			TIMESTAMP_SECONDS
			TIMESTAMP_MILLIS

To demonstrate how datetime functions work, we imported a sample dataset containing datetime data from Clockify to BigQuery using Coupler.io, which is a solution for automating data exports from various applications and sources. It looks like this after import:

Check out the available BigQuery integrations.

Try Coupler.io for free

Let’s begin with some simple but useful examples of how to use these functions.

How to find the current datetime in BigQuery

To get the current date or time expression you can use the CURRENT function in BigQuery. The following statements show the syntax in which the function can be written:

CURRENT_DATE()
CURRENT_DATETIME()
CURRENT_TIMESTAMP()
CURRENT_TIME()

The example below shows how to use the CURRENT_DATETIME function to get today’s date and current time.

SELECT CURRENT_DATETIME() as now;

Note: This returns your computer’s date and time settings (including time zone).

How to change BigQuery datetime format

When dealing with dates and time data, you may want to alter the format in which it appears with the FORMAT function. The FORMAT function formats the date and time according to the specified format_string.

The following statements show the syntax in which the function can be written:

FORMAT_DATE(format_string, date)
FORMAT_DATETIME(format_string, datetime)
FORMAT_TIMESTAMP(format_string, timestamp,[timezone])
FORMAT_TIME(format_string, time)

Below is a list of some supported format strings in BigQuery.

Format String	Description	Example
`%x`	The date represented is in MM/DD/YY format.	04/11/21
`%c`	The date and time are represented using the format strings `%a %b %e %T %Y`.	Wed Jul 17 12:34:12 2021
`%A`	The full weekday name is shown.	Monday
`%d`	The day of the month is represented as a decimal number (01-31).	23
`%M`	The minute is represented as a decimal number (00-59).	36
`%p`	Either AM or PM is shown.	PM

For a more comprehensive list, see Supported Format Elements that you can use with this function.

The example below uses the FORMAT function to change the format in which the date appears. The format string “%x” represents the date in MM/DD/YY format.

SELECT FORMAT_DATE("%x", DATE "2021-07-12" ) AS new_format

Note: FORMAT functions return strings; therefore, using the result of format as a date won’t work.

How to add and subtract your date/time

In BigQuery, we can execute data changes like adding a year to a date, subtracting a week, adding an hour or minute to a time, etc.

Add date/time in BigQuery
To add to a date and time, the following statements show the syntax in which the function can be written:

DATE_ADD(date_expression, INTERVAL int64_expression date_part)
DATETIME_ADD(datetime_expression, INTERVAL int64_expression date_part)
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
TIME_ADD(time_expression, INTERVAL int64_expression date_part)

We use intervals to tell BigQuery how to manipulate the date/time. The int64_expression contains values that range from (-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807). These values are units of date_part that are added to the function, for example: DATETIME_ADD('2012-06-23', INTERVAL 4 MONTH).

The following date_part values are supported:

DAY
HOUR
MINUTE
SECOND
MILLISECOND
MICROSECOND
WEEK
QUARTER
MONTH
YEAR

The example below uses the DATE_ADD function to add a one day interval to the date.

SELECT DATE_ADD(DATE "2021-07-2", INTERVAL 1 DAY) AS one_day_later

Note: if you add “-1” then it will actually subtract one day.

Subtract date/time in BigQuery

To subtract from your date/time, the following statements show the syntax in which the function can be written.

DATE_SUB(date_expression, INTERVAL int64_expression part)
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression part)
TIME_SUB(time_expression, INTERVAL int64_expression part)

We use intervals to tell BigQuery how to manipulate the date/time. The int64_expression contains values that range from (-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807). These values are units of date_part that are subtracted from the date/time expression, for example: DATETIME_SUB(DATETIME "2021-07-21 16:42:05", INTERVAL 1 DAY).

The following date_part values are supported:

DAY
HOUR
MINUTE
SECOND
MILLISECOND
MICROSECOND
WEEK
QUARTER
MONTH
YEAR

The example below uses the DATETIME_SUB function to remove a day interval from the datetime.

SELECT TIME_SUB('10:00:01', INTERVAL 3 HOUR) as hour_earlier

How to group date/time in BigQuery

When evaluating date/time data in BigQuery, we tend to organize and group our data by date parts such as minute, hour, day, year, week, etc.

The following statements show the syntax in which the function can be written:

DATE_TRUNC(date_expression, date_part)
DATETIME_TRUNC(datetime_expression, date_part)
TIMESTAMP_TRUNC(timestamp_expression, date_part)
TIME_TRUNC(time_expression, date_ part)

The date_part values are supported:

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

The example below uses the DATE_TRUNC function to return the first day of the month.

SELECT 
 DATE_TRUNC(DATE '2021-07-25', MONTH) AS first_day_of_month;

In addition, instead of using the TRUNC function, you may use the LAST DAY function to obtain the last day of each date part.

The following statements show the function to get the last day of a date part for a date/time.

LAST_DAY(date_expression, [date_part])
LAST_DAY(datetime_expression, [date_part])

The example below uses the LAST_DAY function to return the last day of the week that starts on Sunday.

SELECT 
  LAST_DAY(DATETIME '2021-07-10 11:45:00', WEEK(SUNDAY))

How to extract date and time parts in BigQuery

You can extract a date part from your datetime value in order to perform some arithmetic with it, such as keeping track of user’s login time or keeping track of overall monthly statistics to see which months have more activities.

The following statements show the syntax in which the function can be written:

EXTRACT(part FROM date_expression)
EXTRACT(part FROM datetime_expression)
EXTRACT(part FROM timestamp_expression)
EXTRACT(part FROM time_expression)

The date part values supported include:

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

The example below uses the EXTRACT function to return a value corresponding to the minute time part.

SELECT 
  EXTRACT(MINUTE FROM DATETIME("2020-12-25 15:30:00")) as minute;

How to calculate the difference between two dates/times?

To calculate the difference between two dates, the following statements show the syntax in which the function can be written:

DATE_DIFF(date_expression_a, date_expression_b, part)
DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)
TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, part)
TIME_DIFF(time_expression_a,time_expression_b, part)

The example below uses the DATE_DIFF function to show the month difference between two dates.

SELECT
 DATE "2021-12-15 12:30:10" as first_date,
 DATE "2021-07-15 17:45:33" as second_date,
 DATE_DIFF(DATE "2021-12-15",
   DATE "2021-07-15", MONTH) as month_difference;

How to convert to date/time data types in BigQuery

When importing data, it is important to be aware of the variations in data types. For successful data synchronizations, the data type of the data you want to import must be compatible with the data type in your BigQuery.

With the CAST function, we can convert from one data type to another. The syntax for the CAST function is:

CAST(expression AS datatype)

Below are some examples of how to use the CAST function

Google BigQuery SQL function: Cast string to date/time

The example below uses the CAST function to convert the string to time:

SELECT
 CAST('19:30:12' AS TIME) AS time

Google BigQuery SQL function: Cast datetime to date

The example below uses the CAST function to convert the current datetime to a date:

SELECT
   CAST(CURRENT_DATETIME() AS DATE);

Note: you can also use the DATE function for this – DATE(datetime expression)

Bigquery Dates Comparisons

In BigQuery, you may sometimes need to compare date and time to do operations such as getting data from the previous week or within a certain range using various comparison operators:

<, <=, >, >=, = , != or <>, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN

The example below uses >= and < operators to get all the specified dates that occur between both dates:

SELECT
  date
FROM
  (
    SELECT
      CAST('2021-07-02' AS DATE) AS date
    UNION ALL
(    SELECT
      CAST('2021-07-11' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2021-10-02' AS DATE) AS date)
  ) AS table_3
WHERE
  ((date >= '2021-07-02') AND (date < '2021-07-30'))

Final Words

If you followed this article up to this point, then you’re already aware of how essential date and time are for data analysis and SQL, as well as how to write actionable queries. This serves as a cheat sheet on the most common use cases of functions that you can work with to manipulate your date and time data. If you do not currently have data in BigQuery, you may use Coupler.io to quickly import it and then begin working on your queries.

Automate data exportd with a Coupler.io

Get started for free

Have fun with your data and good luck!

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼