date_range()

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_range	Start	End
context / doc.date_range	Retrieved from report/dashboard/document (legacy) date picker
between yyyy-mm-dd and yyyy-mm-dd	Start 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 days	Today minus n daysExample - "last 7 days":	End of yesterday (at midnight)
yesterday	Start of yesterday (at midnight)	End of yesterday (at midnight)
today	Start of today (at midnight)	Now
current n days	Today minus (n-1) days	Now
current week	Monday of this weekExample - on Saturday:	Now
current month	First of this month	Now
current quarter	First day of the current quarter:Q1 begins on January 1st and ends on March 31Q2 begins on April 1st and ends on June 30thQ3 begins on July 1st and ends on September 30Q4 begins on October 1st and ends on December 31	Now
current year	January 1st of this year	Now
date_range_start, date_range_end	The 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

date_range_mysql()

<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))

date_range_gcp()

<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))

TABLE_DATE_RANGE()

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'))

current_timestamp()

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.