Datetime Functions in Standard SQL

BigQuery supports the following DATETIME functions.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

Description

Returns the current time as a DATETIME object.

This function supports an optional timezone parameter. See Timezone definitions for information on how to specify a time zone.

Return Data Type

DATETIME

Example

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression, time_expression)
3. DATETIME(timestamp_expression [, timezone])

Description

1. Constructs a DATETIME object using INT64 values representing the year, month, day, hour, minute, and second.
2. Constructs a DATETIME object using a DATE object and a TIME object.
3. Constructs a DATETIME object using a TIMESTAMP object. It supports an optional parameter to specify a timezone. If no timezone is specified, the default timezone, UTC, is used.

Return Data Type

DATETIME

Example

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

Description

Adds INT64_expr units of part to the DATETIME object.

DATETIME_ADD supports the following values for part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalent to 7 DAYs.
• MONTH
• QUARTER
• YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original DATETIME's day, then the result day is the last day of the new month.

Return Data Type

DATETIME

Example

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

Description

Subtracts INT64_expr units of part from the DATETIME.

DATETIME_SUB supports the following values for part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalent to 7 DAYs.
• MONTH
• QUARTER
• YEAR

Special handling is required for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original DATETIME's day, then the result day is the last day of the new month.

Return Data Type

DATETIME

Example

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

Description

Returns the number of part boundaries between the two datetime_expressions. If the first DATETIME occurs before the second DATETIME, then the result is non-positive. Throws an error if the computation overflows the result type, such as if the difference in microseconds between the two DATETIME objects would overflow an INT64 value.

DATETIME_DIFF supports the following values for part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK: This date part begins on Sunday.
• WEEK(<WEEKDAY>): This date part begins on WEEKDAY. Valid values for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
• ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

Return Data Type

INT64

Example

SELECT
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07 10:20:00        | 2008-12-25 15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+

SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

The example above shows the result of DATETIME_DIFF for two DATETIMEs that are 24 hours apart. DATETIME_DIFF with the part WEEK returns 1 because DATETIME_DIFF counts the number of part boundaries in this range of DATETIMEs. Each WEEK begins on Sunday, so there is one part boundary between Saturday, 2017-10-14 00:00:00 and Sunday, 2017-10-15 00:00:00.

The following example shows the result of DATETIME_DIFF for two dates in different years. DATETIME_DIFF with the date part YEAR returns 3 because it counts the number of Gregorian calendar year boundaries between the two DATETIMEs. DATETIME_DIFF with the date part ISOYEAR returns 2 because the second DATETIME belongs to the ISO year 2015. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29.

SELECT
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', YEAR) AS year_diff,
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

The following example shows the result of DATETIME_DIFF for two days in succession. The first date falls on a Monday and the second date falls on a Sunday. DATETIME_DIFF with the date part WEEK returns 0 because this time part uses weeks that begin on Sunday. DATETIME_DIFF with the date part WEEK(MONDAY) returns 1. DATETIME_DIFF with the date part ISOWEEK also returns 1 because ISO weeks begin on Monday.

SELECT
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Description

Truncates a DATETIME object to the granularity of part.

DATETIME_TRUNC supports the following values for part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>): Truncates datetime_expression to the preceding week boundary, where weeks begin on WEEKDAY. Valid values for WEEKDAY are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
• ISOWEEK: Truncates datetime_expression to the preceding ISO 8601 week boundary. ISOWEEKs begin on Monday. The first ISOWEEK of each ISO year contains the first Thursday of the corresponding Gregorian calendar year. Any date_expression earlier than this will truncate to the preceding Monday.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Truncates datetime_expression to the preceding ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

Return Data Type

DATETIME

Examples

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25 15:30:00        | 2008-12-25 00:00:00    |
+----------------------------+------------------------+

In the following example, the original DATETIME falls on a Sunday. Because the part is WEEK(MONDAY), DATE_TRUNC returns the DATETIME for the preceding Monday.

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP '2017-11-05 00:00:00') AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05 00:00:00 | 2017-10-30 00:00:00 |
+---------------------+---------------------+

In the following example, the original datetime_expression is in the Gregorian calendar year 2015. However, DATETIME_TRUNC with the ISOYEAR date part truncates the datetime_expression to the beginning of the ISO year, not the Gregorian calendar year. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29. Therefore the ISO year boundary preceding the datetime_expression 2015-06-15 00:00:00 is 2014-12-29.

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

+---------------------+----------------+
| isoyear_boundary    | isoyear_number |
+---------------------+----------------+
| 2014-12-29 00:00:00 | 2015           |
+---------------------+----------------+

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Description

Formats a DATETIME object according to the specified format_string. See Supported Format Elements For DATETIME for a list of format elements that this function supports.

Return Data Type

STRING

Examples

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

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

SELECT
  FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+

SELECT
  FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

Description

Uses a format_string and a STRING representation of a DATETIME to return a DATETIME. See Supported Format Elements For DATETIME for a list of format elements that this function supports.

PARSE_DATETIME parses string according to the following rules:

• Unspecified fields. Any unspecified field is initialized from 1970-01-01 00:00:00.0. For example, if the year is unspecified then it defaults to 1970.
• Case insensitive names. Names, such as Monday and February, are case insensitive.
• Whitespace. One or more consecutive white spaces in the format string matches zero or more consecutive white spaces in the DATETIME string. Leading and trailing white spaces in the DATETIME string are always allowed—even if they are not in the format string.
• Format precedence. When two or more format elements have overlapping information, the last one generally overrides any earlier ones, with some exceptions. For example, both %F and %Y affect the year, so the earlier element overrides the later. See the descriptions of %s, %C, and %y in Supported Format Elements For DATETIME.

Note: This function supports format elements, but does not have full support for %Q, %a, %A, %g, %G, %j, %u, %U, %V, %w, and %W.

Examples

The following example parses a STRING literal as a DATETIME.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

The above query returns the following output:

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18 13:45:55 |
+---------------------+

The following example parses a STRING literal containing a date in a natural language format as a DATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
  AS datetime;

The above query returns the following output:

+---------------------+
| datetime            |
+---------------------+
| 2018-12-19 00:00:00 |
+---------------------+

Return Data Type

DATETIME

Supported format elements for DATETIME

Unless otherwise noted, DATETIME functions that use format strings support the following elements:

Format element	Description
%A	The full weekday name.
%a	The abbreviated weekday name.
%B	The full month name.
%b or %h	The abbreviated month name.
%C	The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99).
%c	The date and time representation.
%D	The date in the format %m/%d/%y.
%d	The day of the month as a decimal number (01-31).
%e	The day of month as a decimal number (1-31); single digits are preceded by a space.
%F	The date in the format %Y-%m-%d.
%G	The ISO 8601 year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.
%g	The ISO 8601 year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge.
%H	The hour (24-hour clock) as a decimal number (00-23).
%I	The hour (12-hour clock) as a decimal number (01-12).
%j	The day of the year as a decimal number (001-366).
%k	The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space.
%l	The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space.
%M	The minute as a decimal number (00-59).
%m	The month as a decimal number (01-12).
%n	A newline character.
%P	Either am or pm.
%p	Either AM or PM.
%Q	The quarter as a decimal number (1-4).
%R	The time in the format %H:%M.
%r	The 12-hour clock time using AM/PM notation.
%S	The second as a decimal number (00-60).
%s	The number of seconds since 1970-01-01 00:00:00. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence.
%T	The time in the format %H:%M:%S.
%t	A tab character.
%U	The week number of the year (Sunday as the first day of the week) as a decimal number (00-53).
%u	The weekday (Monday as the first day of the week) as a decimal number (1-7).
%V	The ISO 8601 week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it is week 1; otherwise it is week 53 of the previous year, and the next week is week 1.
%W	The week number of the year (Monday as the first day of the week) as a decimal number (00-53).
%w	The weekday (Sunday as the first day of the week) as a decimal number (0-6).
%X	The time representation in HH:MM:SS format.
%x	The date representation in MM/DD/YY format.
%Y	The year with century as a decimal number.
%y	The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C is not specified, years 00-68 are 2000s, while years 69-99 are 1900s.
%%	A single % character.
%E#S	Seconds with # digits of fractional precision.
%E*S	Seconds with full fractional precision (a literal '*').
%E4Y	Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year.