Back to Blog

Google BigQuery Datetime and BigQuery Timestamp Functions Explained

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. 

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_DATECURRENT_TIMECURRENT_DATECURRENT_TIMESTAMP
DATETIMEDATETIMETIMESTAMP
EXTRACTEXTRACTEXTRACTEXTRACT
DATE_ADDTIME_ADDDATETIME_ADDTIMESTAMP_ADD
DATE_SUBTIME_SUBDATETIME_SUBTIMESTAMP_SUB
DATE_DIFFTIME_DIFFDATETIME_DIFFTIMESTAMP_DIFF
DATE_TRUNCTIME_TRUNCDATETIME_TRUNCTIMESTAMP_TRUNC
FORMAT_DATEFORMAT_TIMEFORMAT_DATETIMEFORMAT_TIMESTAMP
PARSE_DATEPARSE_TIMEPARSE_DATETIMEPARSE_TIMESTAMP
LAST_DAYLAST_DAY
DATE_FROM_UNIX_DATEDATETIME_FROM_UNIX_DATE
UNIX_DATEUNIX_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.

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
%xThe date represented is in MM/DD/YY format.04/11/21
%cThe date and time are represented using the format strings %a %b %e %T %Y.Wed Jul 17 12:34:12 2021
%AThe full weekday name is shown.Monday
%dThe day of the month is represented as a decimal number (01-31).23
%MThe minute is represented as a decimal number (00-59).36
%pEither 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.

Have fun with your data and good luck!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free