GoogleSQL for BigQuery supports the following timestamp functions.
IMPORTANT: Before working with these functions, you need to understand the difference between the formats in which timestamps are stored and displayed, and how time zones are used for the conversion between these formats. To learn more, see How time zones work with timestamp functions.
NOTE: These functions return a runtime error if overflow occurs; result
values are bounded by the defined DATE
range
and TIMESTAMP
range.
Function list
Name | Summary |
---|---|
CURRENT_TIMESTAMP
|
Returns the current date and time as a TIMESTAMP object.
|
EXTRACT
|
Extracts part of a TIMESTAMP value.
|
FORMAT_TIMESTAMP
|
Formats a TIMESTAMP value according to the specified
format string.
|
PARSE_TIMESTAMP
|
Converts a STRING value to a TIMESTAMP value.
|
STRING
|
Converts a TIMESTAMP value to a STRING value.
|
TIMESTAMP
|
Constructs a TIMESTAMP value.
|
TIMESTAMP_ADD
|
Adds a specified time interval to a TIMESTAMP value.
|
TIMESTAMP_DIFF
|
Gets the number of intervals between two TIMESTAMP values.
|
TIMESTAMP_MICROS
|
Converts the number of microseconds since
1970-01-01 00:00:00 UTC to a TIMESTAMP.
|
TIMESTAMP_MILLIS
|
Converts the number of milliseconds since
1970-01-01 00:00:00 UTC to a TIMESTAMP.
|
TIMESTAMP_SECONDS
|
Converts the number of seconds since
1970-01-01 00:00:00 UTC to a TIMESTAMP.
|
TIMESTAMP_SUB
|
Subtracts a specified time interval from a TIMESTAMP value.
|
TIMESTAMP_TRUNC
|
Truncates a TIMESTAMP value.
|
UNIX_MICROS
|
Converts a TIMESTAMP value to the number of microseconds since
1970-01-01 00:00:00 UTC.
|
UNIX_MILLIS
|
Converts a TIMESTAMP value to the number of milliseconds
since 1970-01-01 00:00:00 UTC.
|
UNIX_SECONDS
|
Converts a TIMESTAMP value to the number of seconds since
1970-01-01 00:00:00 UTC.
|
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP
Description
Returns the current date and time as a timestamp object. The timestamp is continuous, non-ambiguous, has exactly 60 seconds per minute and does not repeat values over the leap second. Parentheses are optional.
This function handles leap seconds by smearing them across a window of 20 hours around the inserted leap second.
The current date and time is recorded at the start of the query statement which contains this function, not when this specific function is evaluated.
Supported Input Types
Not applicable
Result Data Type
TIMESTAMP
Examples
SELECT CURRENT_TIMESTAMP() AS now;
/*--------------------------------*
| now |
+--------------------------------+
| 2020-06-02 23:57:12.120174 UTC |
*--------------------------------*/
When a column named current_timestamp
is present, the column name and the
function call without parentheses are ambiguous. To ensure the function call,
add parentheses; to ensure the column name, qualify it with its
range variable. For example, the
following query selects the function in the now
column and the table
column in the current_timestamp
column.
WITH t AS (SELECT 'column value' AS `current_timestamp`)
SELECT current_timestamp() AS now, t.current_timestamp FROM t;
/*--------------------------------+-------------------*
| now | current_timestamp |
+--------------------------------+-------------------+
| 2020-06-02 23:57:12.120174 UTC | column value |
*--------------------------------+-------------------*/
EXTRACT
EXTRACT(part FROM timestamp_expression [AT TIME ZONE time_zone])
Description
Returns a value that corresponds to the specified part
from
a supplied timestamp_expression
. This function supports an optional
time_zone
parameter. See
Time zone definitions for information
on how to specify a time zone.
Allowed part
values are:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
DAYOFWEEK
: Returns values in the range [1,7] with Sunday as the first day of of the week.DAY
DAYOFYEAR
WEEK
: Returns the week number of the date in the range [0, 53]. Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0.WEEK(<WEEKDAY>)
: Returns the week number oftimestamp_expression
in the range [0, 53]. Weeks begin onWEEKDAY
.datetime
s prior to the firstWEEKDAY
of the year are in week 0. Valid values forWEEKDAY
areSUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, andSATURDAY
.ISOWEEK
: Returns the ISO 8601 week number of thedatetime_expression
.ISOWEEK
s begin on Monday. Return values are in the range [1, 53]. The firstISOWEEK
of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.MONTH
QUARTER
YEAR
ISOYEAR
: Returns the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to whichdate_expression
belongs.DATE
DATETIME
TIME
Returned values truncate lower order time periods. For example, when extracting
seconds, EXTRACT
truncates the millisecond and microsecond values.
Return Data Type
INT64
, except in the following cases:
- If
part
isDATE
, the function returns aDATE
object.
Examples
In the following example, EXTRACT
returns a value corresponding to the DAY
time part.
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
/*-------------+--------------------*
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25 | 24 |
*-------------+--------------------*/
In the following example, EXTRACT
returns values corresponding to different
time parts from a column of type TIMESTAMP
.
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;
-- 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 |
*-------------------------+---------+---------+------+------*/
In the following example, timestamp_expression
falls on a Monday. EXTRACT
calculates the first column using weeks that begin on Sunday, and it calculates
the second column using weeks that begin on Monday.
WITH 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;
-- 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 |
*-------------------------+-------------+---------------*/
FORMAT_TIMESTAMP
FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])
Description
Formats a timestamp according to the specified format_string
.
See Format elements for date and time parts for a list of format elements that this function supports.
Return Data Type
STRING
Example
SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2050-12-25 15:30:55+00", "UTC")
AS formatted;
/*--------------------------*
| formatted |
+--------------------------+
| Sun Dec 25 15:30:55 2050 |
*--------------------------*/
SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2050-12-25 15:30:55+00")
AS formatted;
/*-------------*
| formatted |
+-------------+
| Dec-25-2050 |
*-------------*/
SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2050-12-25 15:30:55+00")
AS formatted;
/*-------------*
| formatted |
+-------------+
| Dec 2050 |
*-------------*/
SELECT FORMAT_TIMESTAMP("%Y-%m-%dT%H:%M:%SZ", TIMESTAMP "2050-12-25 15:30:55", "UTC")
AS formatted;
/*+---------------------*
| formatted |
+----------------------+
| 2050-12-25T15:30:55Z |
*----------------------*/
PARSE_TIMESTAMP
PARSE_TIMESTAMP(format_string, timestamp_string[, time_zone])
Description
Converts a string representation of a timestamp to a
TIMESTAMP
object.
format_string
contains the format elements
that define how timestamp_string
is formatted. Each element in
timestamp_string
must have a corresponding element in format_string
. The
location of each element in format_string
must match the location of
each element in timestamp_string
.
-- 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 produces an error 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 produces an error 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")
The format string fully supports most format elements, except for
%P
.
When using PARSE_TIMESTAMP
, keep the following in mind:
- Unspecified fields. Any unspecified field is 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 present. If not, the initialization value uses the default time zone, UTC. For instance, if the year is unspecified then it defaults to1970
, and so on. - Case insensitivity. Names, such as
Monday
,February
, and so on, are case insensitive. - Whitespace. One or more consecutive white spaces in the format string matches zero or more consecutive white spaces in the timestamp string. In addition, leading and trailing white spaces in the timestamp string are always allowed, even if they are not in the format string.
- Format precedence. When two (or more) format elements have overlapping
information (for example both
%F
and%Y
affect the year), the last one generally overrides any earlier ones, with some exceptions (see the descriptions of%s
,%C
, and%y
). - Format divergence.
%p
can be used witham
,AM
,pm
, andPM
.
Return Data Type
TIMESTAMP
Example
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| parsed |
+-------------------------+
| 2008-12-25 07:30:00 UTC |
*-------------------------*/
STRING
STRING(timestamp_expression[, time_zone])
Description
Converts a timestamp to a string. Supports an optional parameter to specify a time zone. See Time zone definitions for information on how to specify a time zone.
Return Data Type
STRING
Example
SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;
/*-------------------------------*
| string |
+-------------------------------+
| 2008-12-25 15:30:00+00 |
*-------------------------------*/
TIMESTAMP
TIMESTAMP(string_expression[, time_zone])
TIMESTAMP(date_expression[, time_zone])
TIMESTAMP(datetime_expression[, time_zone])
Description
string_expression[, time_zone]
: Converts a string to a timestamp.string_expression
must include a timestamp literal. Ifstring_expression
includes a time zone in the timestamp literal, do not include an explicittime_zone
argument.date_expression[, time_zone]
: Converts a date to a timestamp. The value returned is the earliest timestamp that falls within the given date.datetime_expression[, time_zone]
: Converts a datetime to a timestamp.
This function supports an optional parameter to specify a time zone. If no time zone is specified, the default time zone, UTC, is used.
Return Data Type
TIMESTAMP
Examples
SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;
-- 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 |
*-------------------------*/
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;
-- 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 |
*-------------------------*/
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;
-- 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 |
*-------------------------*/