GoogleSQL for Bigtable 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 unit boundaries between two TIMESTAMP values
at a particular time granularity.
|
TIMESTAMP_FROM_UNIX_MICROS
|
Similar to TIMESTAMP_MICROS , except that additionally, a
TIMESTAMP value can be passed in.
|
TIMESTAMP_FROM_UNIX_MILLIS
|
Similar to TIMESTAMP_MILLIS , except that additionally, a
TIMESTAMP value can be passed in.
|
TIMESTAMP_FROM_UNIX_SECONDS
|
Similar to TIMESTAMP_SECONDS , except that additionally, a
TIMESTAMP value can be passed in.
|
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 or
DATETIME value at a particular
granularity.
|
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 |
*--------------------------------*/
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
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.
SELECT
EXTRACT(
DAY
FROM TIMESTAMP('2008-12-25 05:30:00+00') AT TIME ZONE 'UTC')
AS the_day_utc,
EXTRACT(
DAY
FROM TIMESTAMP('2008-12-25 05:30:00+00') AT TIME ZONE 'America/Los_Angeles')
AS the_day_california
/*-------------+--------------------*
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25 | 24 |
*-------------+--------------------*/
In the following examples, EXTRACT
returns values corresponding to different
time parts from a column of type TIMESTAMP
.
SELECT
EXTRACT(ISOYEAR FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS isoyear,
EXTRACT(ISOWEEK FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS isoweek,
EXTRACT(YEAR FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS year,
EXTRACT(WEEK FROM TIMESTAMP("2005-01-03 12:34:56+00")) AS week
-- Display of results may differ, depending upon the environment and
-- time zone where this query was executed.
/*---------+---------+------+------*
| isoyear | isoweek | year | week |
+---------+---------+------+------+
| 2005 | 1 | 2005 | 1 |
*---------+---------+------+------*/
SELECT
TIMESTAMP("2007-12-31 12:00:00+00") AS timestamp_value,
EXTRACT(ISOYEAR FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS isoyear,
EXTRACT(ISOWEEK FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS isoweek,
EXTRACT(YEAR FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS year,
EXTRACT(WEEK FROM TIMESTAMP("2007-12-31 12:00:00+00")) AS week
-- Display of results may differ, depending upon the environment and time zone
-- where this query was executed.
/*---------+---------+------+------*
| isoyear | isoweek | year | week |
+---------+---------+------+------+
| 2008 | 1 | 2007 | 52 |
*---------+---------+------+------*/
SELECT
TIMESTAMP("2009-01-01 12:00:00+00") AS timestamp_value,
EXTRACT(ISOYEAR FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS isoyear,
EXTRACT(ISOWEEK FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS isoweek,
EXTRACT(YEAR FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS year,
EXTRACT(WEEK FROM TIMESTAMP("2009-01-01 12:00:00+00")) AS week
-- Display of results may differ, depending upon the environment and time zone
-- where this query was executed.
/*---------+---------+------+------*
| isoyear | isoweek | year | week |
+---------+---------+------+------+
| 2009 | 1 | 2009 | 0 |
*---------+---------+------+------*/
SELECT
TIMESTAMP("2009-12-31 12:00:00+00") AS timestamp_value,
EXTRACT(ISOYEAR FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS isoyear,
EXTRACT(ISOWEEK FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS isoweek,
EXTRACT(YEAR FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS year,
EXTRACT(WEEK FROM TIMESTAMP("2009-12-31 12:00:00+00")) AS week
-- Display of results may differ, depending upon the environment and time zone
-- where this query was executed.
/*---------+---------+------+------*
| isoyear | isoweek | year | week |
+---------+---------+------+------+
| 2009 | 53 | 2009 | 52 |
*---------+---------+------+------*/
SELECT
TIMESTAMP("2017-01-02 12:00:00+00") AS timestamp_value,
EXTRACT(ISOYEAR FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS isoyear,
EXTRACT(ISOWEEK FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS isoweek,
EXTRACT(YEAR FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS year,
EXTRACT(WEEK FROM TIMESTAMP("2017-01-02 12:00:00+00")) AS week
-- Display of results may differ, depending upon the environment and time zone
-- where this query was executed.
/*---------+---------+------+------*
| isoyear | isoweek | year | week |
+---------+---------+------+------+
| 2017 | 1 | 2017 | 1 |
*---------+---------+------+------*/
SELECT
TIMESTAMP("2017-05-26 12:00:00+00") AS timestamp_value,
EXTRACT(ISOYEAR FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS isoyear,
EXTRACT(ISOWEEK FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS isoweek,
EXTRACT(YEAR FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS year,
EXTRACT(WEEK FROM TIMESTAMP("2017-05-26 12:00:00+00")) AS week
-- Display of results may differ, depending upon the environment and time zone
-- where this query was executed.
/*---------+---------+------+------*
| isoyear | isoweek | year | week |
+---------+---------+------+------+
| 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.
SELECT
EXTRACT(WEEK(SUNDAY) FROM TIMESTAMP("2017-11-06 00:00:00+00")) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM TIMESTAMP("2017-11-06 00:00:00+00")) AS week_monday
-- Display of results may differ, depending upon the environment and time zone
-- where this query was executed.
/*-------------+---------------*
| week_sunday | week_monday |
+-------------+---------------+
| 45 | 44 |
*-------------+---------------*/
FORMAT_TIMESTAMP
FORMAT_TIMESTAMP(format_string, timestamp_expr[, time_zone])
Description
Formats a TIMESTAMP
value according to the specified format string.
Definitions
format_string
: ASTRING
value that contains the format elements to use withtimestamp_expr
.timestamp_expr
: ATIMESTAMP
value that represents the timestamp to format.time_zone
: ASTRING
value that represents a time zone. For more information about how to use a time zone with a timestamp, see Time zone definitions.
Return Data Type
STRING
Examples
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
value to a TIMESTAMP
value.
Definitions
format_string
: ASTRING
value that contains the format elements to use withtimestamp_string
.timestamp_string
: ASTRING
value that represents the timestamp to parse.time_zone
: ASTRING
value that represents a time zone. For more information about how to use a time zone with a timestamp, see Time zone definitions.
Details
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])
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.
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 |
*-------------------------*/
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_date |
+-------------------------+
| 2008-12-25 00:00:00 UTC |
*-------------------------*/
TIMESTAMP_ADD
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
Description
Adds int64_expression
units of date_part
to the timestamp, independent of
any time zone.
TIMESTAMP_ADD
supports the following values for date_part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
. Equivalent to 60MINUTE
parts.DAY
. Equivalent to 24HOUR
parts.
Return Data Types
TIMESTAMP
Example
SELECT
TIMESTAMP("2008-12-25 15:30:00+00") AS original,
TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+-------------------------*
| original | later |
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:40:00 UTC |
*-------------------------+-------------------------*/
TIMESTAMP_DIFF
TIMESTAMP_DIFF(end_timestamp, start_timestamp, granularity)
Description
Gets the number of unit boundaries between two TIMESTAMP
values
(end_timestamp
- start_timestamp
) at a particular time granularity.
Definitions
start_timestamp
: The startingTIMESTAMP
value.end_timestamp
: The endingTIMESTAMP
value.granularity
: The timestamp part that represents the granularity. This can be:MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
. Equivalent to 60MINUTE
s.DAY
. Equivalent to 24HOUR
s.
Details
If end_timestamp
is earlier than start_timestamp
, the output is negative.
Produces an error if the computation overflows, such as if the difference
in microseconds
between the two TIMESTAMP
values overflows.
Return Data Type
INT64
Example
SELECT
TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+-------------------------+-------*
| later_timestamp | earlier_timestamp | hours |
+-------------------------+-------------------------+-------+
| 2010-07-07 10:20:00 UTC | 2008-12-25 15:30:00 UTC | 13410 |
*-------------------------+-------------------------+-------*/
In the following example, the first timestamp occurs before the second timestamp, resulting in a negative output.
SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY) AS negative_diff;
/*---------------*
| negative_diff |
+---------------+
| -61 |
*---------------*/
In this example, the result is 0 because only the number of whole specified
HOUR
intervals are included.
SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR) AS diff;
/*---------------*
| diff |
+---------------+
| 0 |
*---------------*/
TIMESTAMP_FROM_UNIX_MICROS
TIMESTAMP_FROM_UNIX_MICROS(int64_expression)
TIMESTAMP_FROM_UNIX_MICROS(timestamp_expression)
Description
Interprets int64_expression
as the number of microseconds since
1970-01-01 00:00:00 UTC and returns a timestamp. If a timestamp is passed in,
the same timestamp is returned.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_FROM_UNIX_MICROS(1230219000000000) AS timestamp_value;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_value |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
TIMESTAMP_FROM_UNIX_MILLIS
TIMESTAMP_FROM_UNIX_MILLIS(int64_expression)
TIMESTAMP_FROM_UNIX_MILLIS(timestamp_expression)
Description
Interprets int64_expression
as the number of milliseconds since
1970-01-01 00:00:00 UTC and returns a timestamp. If a timestamp is passed in,
the same timestamp is returned.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_FROM_UNIX_MILLIS(1230219000000) AS timestamp_value;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_value |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
TIMESTAMP_FROM_UNIX_SECONDS
TIMESTAMP_FROM_UNIX_SECONDS(int64_expression)
TIMESTAMP_FROM_UNIX_SECONDS(timestamp_expression)
Description
Interprets int64_expression
as the number of seconds since
1970-01-01 00:00:00 UTC and returns a timestamp. If a timestamp is passed in,
the same timestamp is returned.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_FROM_UNIX_SECONDS(1230219000) AS timestamp_value;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_value |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
TIMESTAMP_MICROS
TIMESTAMP_MICROS(int64_expression)
Description
Interprets int64_expression
as the number of microseconds since 1970-01-01
00:00:00 UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_value |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
TIMESTAMP_MILLIS
TIMESTAMP_MILLIS(int64_expression)
Description
Interprets int64_expression
as the number of milliseconds since 1970-01-01
00:00:00 UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_value |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
TIMESTAMP_SECONDS
TIMESTAMP_SECONDS(int64_expression)
Description
Interprets int64_expression
as the number of seconds since 1970-01-01 00:00:00
UTC and returns a timestamp.
Return Data Type
TIMESTAMP
Example
SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------*
| timestamp_value |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
*-------------------------*/
TIMESTAMP_SUB
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)
Description
Subtracts int64_expression
units of date_part
from the timestamp,
independent of any time zone.
TIMESTAMP_SUB
supports the following values for date_part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
. Equivalent to 60MINUTE
parts.DAY
. Equivalent to 24HOUR
parts.
Return Data Type
TIMESTAMP
Example
SELECT
TIMESTAMP("2008-12-25 15:30:00+00") AS original,
TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+-------------------------*
| original | earlier |
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:20:00 UTC |
*-------------------------+-------------------------*/
TIMESTAMP_TRUNC
TIMESTAMP_TRUNC(timestamp_value, timestamp_granularity[, time_zone])
TIMESTAMP_TRUNC(datetime_value, datetime_granularity)
Description
Truncates a TIMESTAMP
or DATETIME
value at a particular granularity.
Definitions
timestamp_value
: ATIMESTAMP
value to truncate.timestamp_granularity
: The truncation granularity for aTIMESTAMP
value. Date granularities and time granularities can be used.time_zone
: A time zone to use with theTIMESTAMP
value. Time zone parts can be used. Use this argument if you want to use a time zone other than the default time zone, UTC, as part of the truncate operation.datetime_value
: ADATETIME
value to truncate.datetime_granularity
: The truncation granularity for aDATETIME
value. Date granularities and time granularities can be used.
Date granularity definitions
DAY
: The day in the Gregorian calendar year that contains the value to truncate.WEEK
: The first day in the week that contains the value to truncate. Weeks begin on Sundays.WEEK
is equivalent toWEEK(SUNDAY)
.WEEK(WEEKDAY)
: The first day in the week that contains the value to truncate. Weeks begin onWEEKDAY
.WEEKDAY
must be one of the following:SUNDAY
,MONDAY
,TUESDAY
,WEDNESDAY
,THURSDAY
,FRIDAY
, orSATURDAY
.ISOWEEK
: The first day in the ISO 8601 week that contains the value to truncate. The ISO week begins on Monday. The first ISO week of each ISO year contains the first Thursday of the corresponding Gregorian calendar year.MONTH
: The first day in the month that contains the value to truncate.QUARTER
: The first day in the quarter that contains the value to truncate.YEAR
: The first day in the year that contains the value to truncate.ISOYEAR
: The first day in the ISO 8601 week-numbering year that contains the value to truncate. The ISO year is the Monday of the first week where Thursday belongs to the corresponding Gregorian calendar year.
Time granularity definitions
MICROSECOND
: If used, nothing is truncated from the value.MILLISECOND
: The nearest lesser than or equal millisecond.SECOND
: The nearest lesser than or equal second.MINUTE
: The nearest lesser than or equal minute.HOUR
: The nearest lesser than or equal hour.
Time zone part definitions
MINUTE
HOUR
DAY
WEEK
WEEK(<WEEKDAY>)
ISOWEEK
MONTH
QUARTER
YEAR
ISOYEAR
Details
The resulting value is always rounded to the beginning of granularity
.
Return Data Type
The same data type as the first argument passed into this function.
Examples
SELECT
TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "UTC") AS utc,
TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+-------------------------*
| utc | la |
+-------------------------+-------------------------+
| 2008-12-25 00:00:00 UTC | 2008-12-25 08:00:00 UTC |
*-------------------------+-------------------------*/
In the following example, timestamp_expression
has a time zone offset of +12.
The first column shows the timestamp_expression
in UTC time. The second
column shows the output of TIMESTAMP_TRUNC
using weeks that start on Monday.
Because the timestamp_expression
falls on a Sunday in UTC, TIMESTAMP_TRUNC
truncates it to the preceding Monday. The third column shows the same function
with the optional Time zone definition
argument 'Pacific/Auckland'. Here, the function truncates the
timestamp_expression
using New Zealand Daylight Time, where it falls on a
Monday.
SELECT
timestamp_value AS timestamp_value,
TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "UTC") AS utc_truncated,
TIMESTAMP_TRUNC(timestamp_value, WEEK(MONDAY), "Pacific/Auckland") AS nzdt_truncated
FROM (SELECT TIMESTAMP("2017-11-06 00:00:00+12") AS timestamp_value);
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+-------------------------+-------------------------*
| timestamp_value | utc_truncated | nzdt_truncated |
+-------------------------+-------------------------+-------------------------+
| 2017-11-05 12:00:00 UTC | 2017-10-30 00:00:00 UTC | 2017-11-05 11:00:00 UTC |
*-------------------------+-------------------------+-------------------------*/
In the following example, the original timestamp_expression
is in the
Gregorian calendar year 2015. However, TIMESTAMP_TRUNC
with the ISOYEAR
date
part truncates the timestamp_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 timestamp_expression
2015-06-15 00:00:00+00 is 2014-12-29.
SELECT
TIMESTAMP_TRUNC("2015-06-15 00:00:00+00", ISOYEAR) AS isoyear_boundary,
EXTRACT(ISOYEAR FROM TIMESTAMP "2015-06-15 00:00:00+00") AS isoyear_number;
-- Display of results may differ, depending upon the environment and time zone where this query was executed.
/*-------------------------+----------------*
| isoyear_boundary | isoyear_number |
+-------------------------+----------------+
| 2014-12-29 00:00:00 UTC | 2015 |
*-------------------------+----------------*/
UNIX_MICROS
UNIX_MICROS(timestamp_expression)
Description
Returns the number of microseconds since 1970-01-01 00:00:00 UTC
.
Return Data Type
INT64
Examples
SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") AS micros;
/*------------------*
| micros |
+------------------+
| 1230219000000000 |
*------------------*/
UNIX_MILLIS
UNIX_MILLIS(timestamp_expression)
Description
Returns the number of milliseconds since 1970-01-01 00:00:00 UTC
. Truncates
higher levels of precision by rounding down to the beginning of the millisecond.
Return Data Type
INT64
Examples
SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00+00") AS millis;
/*---------------*
| millis |
+---------------+
| 1230219000000 |
*---------------*/
SELECT UNIX_MILLIS(TIMESTAMP "1970-01-01 00:00:00.0018+00") AS millis;
/*---------------*
| millis |
+---------------+
| 1 |
*---------------*/
UNIX_SECONDS
UNIX_SECONDS(timestamp_expression)
Description
Returns the number of seconds since 1970-01-01 00:00:00 UTC
. Truncates higher
levels of precision by rounding down to the beginning of the second.
Return Data Type
INT64
Examples
SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS seconds;
/*------------*
| seconds |
+------------+
| 1230219000 |
*------------*/
SELECT UNIX_SECONDS(TIMESTAMP "1970-01-01 00:00:01.8+00") AS seconds;
/*------------*
| seconds |
+------------+
| 1 |
*------------*/
Supplemental materials
How time zones work with timestamp functions
A timestamp represents an absolute point in time, independent of any time
zone. However, when a timestamp value is displayed, it is usually converted to
a human-readable format consisting of a civil date and time
(YYYY-MM-DD HH:MM:SS)
and a time zone. This is not the internal representation of the
TIMESTAMP
; it is only a human-understandable way to describe the point in time
that the timestamp represents.
Some timestamp functions have a time zone argument. A time zone is needed to
convert between civil time (YYYY-MM-DD HH:MM:SS) and the absolute time
represented by a timestamp.
A function like PARSE_TIMESTAMP
takes an input string that represents a
civil time and returns a timestamp that represents an absolute time. A
time zone is needed for this conversion. A function like EXTRACT
takes an
input timestamp (absolute time) and converts it to civil time in order to
extract a part of that civil time. This conversion requires a time zone.
If no time zone is specified, the default time zone, UTC,
is used.
Certain date and timestamp functions allow you to override the default time zone
and specify a different one. You can specify a time zone by either supplying
the time zone name (for example, America/Los_Angeles
)
or time zone offset from UTC (for example, -08).
To learn more about how time zones work with the TIMESTAMP
type, see
Time zones.