Date and time functions manipulate and convert data representations between UNIX timestamps (which are commonly used in logs), string data representation (usually referred to as human readable date), and the platform basic date type of TIMESTAMP.
Unix timestamps refer to Unix time (defined as the number of seconds that have elapsed since midnight UTC, 1 January 1970) as a single signed integer number that increments every second. There is no need to perform calculations to determine year, month, day of month, hour, and minute required for human intelligibility. A day is almost always 86400 seconds long, but due to leap seconds, it is occasionally 86401 seconds. The UTC time zone is used when date values are returned by queries.

Function:

date_range()

date_range is used to provide a simple and intuitive way to apply a period filter to a CQL query. It is also used by the partitioning module to select the minimal set of relevant partitions for the query. This function allows you to make a simple declaration of the time slice (filter) for each query.

Supported date_range syntax is as follows:

where date_range(range)

A date range must be specified in all queries running “FROM CoolaData”, in the WHERE clause. Alternatively to using one of the the following “date_range()” expressions, you can explicitly specify the event_time_ts range, as follows:

WHERE event_time_ts BETWEEN TIMESTAMP('2015-01-01') AND TIMESTAMP('2015-12-31')

The following table describes the supported date_range range terms:

date_rangeStartEnd
context / doc.date_rangeRetrieved from report/dashboard/document (legacy) date picker
between yyyy-mm-dd and yyyy-mm-ddStart of the first date (at midnight)End of the second date (at midnight)
between {date_function} and {date_function}Supported date functions:
NOW()
GET_DATE()
DATE_ADD(date,value,HOUR|MINUTE|DAY|MONTH|YEAR)
Support dates formats:
yyyy-dd-mm
"yyyy-dd-mm"
TIMESTAMP("yyyy-dd-mm")
last n daysToday minus n days
Example - "last 7 days": date_range_last
End of yesterday (at midnight)
yesterdayStart of yesterday (at midnight)End of yesterday (at midnight)
todayStart of today (at midnight)Now
current n daysToday minus (n-1) daysNow
current weekMonday of this week
Example - on Saturday:
date_range_current
Now
current monthFirst of this monthNow
current quarter First day of the current quarter:
Q1 begins on January 1st and ends on March 31
Q2 begins on April 1st and ends on June 30th
Q3 begins on July 1st and ends on September 30
Q4 begins on October 1st and ends on December 31
Now
current yearJanuary 1st of this yearNow
date_range_start, date_range_endThe context (report/dashboard) date range start date, as a timestamp. Both expressions can be used anywhere in the query, together or separately, and with any date function. Example: table_date_range(my_table, date_range_start, date_range_end)The context (report/dashboard) date range end date

Function:

<data_column>(date_range_mysql(context))

This function returns the report/document date range in MySQL format for the stated data column.

WHERE created_date(date_range_mysql(context))
|
-->  WHERE created_date BETWEEN <start date> and <end date>

Note: date_range_mysql works with a date input, in the format of YYYY-MM-DD. Use the DATE() function if the datatype is timestamp to convert it to this format:

WHERE DATE(event_time_ts)(date_range_mysql(context))

Function:

<data_column>(date_range_gcp(context))

This function returns the report/document date range in Google Cloud Platform format for the stated data column.

WHERE create_date(date_range_gcp(context))
|
-->  WHERE create_date BETWEEN '<start date>' and '<end date>'

Note: date_range_gcp works with a date input, in the format of YYYY-MM-DD. Use the DATE() function if the datatype is timestamp to convert it to this format:

WHERE DATE(event_time_ts)(date_range_gcp(context))

Function:

TABLE_DATE_RANGE(<table_name>, timestamp, timestamp)

Use this function to query from consecutive daily tables. Table names must have the following format: <prefix><date>, where <date> is in the format YYYYMMDD. You can use date and time functions on the timestamp parameters. Example: to query all sales tables from Jan 1st, 2016 to last week (“sales20160101”, “sales20160102”, etc.) use this query:

select count(*)
from TABLE_DATE_RANGE(sales,TIMESTAMP('2016-01-01'),DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'))

Function:

current_timestamp()

This function returns current time as a string formatted to YYYY-mm-dd HH:MM:SS UTC.

SELECT current_timestamp();
|
--> 2013-05-13 12:05:25 UTC

 

See Big Query query reference for more information on all supported date and time functions.

Print Friendly, PDF & Email