Date and Time Functions

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

Server Side Join

CoolaSQL provides the ability to run server side join. Server side join is useful when you need to either join two or more queries, or CoolaData and a custom table.

CoolaSQL supports the following JOIN types:

  • JOIN
  • INNER JOIN
  • LEFT JOIN
  • LEFT OUTER JOIN
  • RIGHT JOIN
  • RIGHT OUTER JOIN

Note: Make sure to provide aliases to all data columns in the top select when performing a join.

Example: Using INNER JOIN

SELECT a.ip_country AS country
    ,a.sessions AS current_week_sessions
    ,b.sessions AS previous_week_sessions
FROM (
    SELECT ip_country
        ,count(session_id) AS sessions
    FROM cooladata
    WHERE date_range(CURRENT WEEK)
    GROUP BY ip_country
    ) a
INNER JOIN (
    SELECT ip_country
        ,count(session_id) AS sessions
    FROM cooladata
    WHERE date_range(PREVIOUS WEEK)
    GROUP BY ip_country
    ) b ON a.ip_country = b.ip_country

 

Print Friendly, PDF & Email

Other Functions

Function: SELECT *

Use this function to return all results from a certain sub-query.
Note: This function is only supported  when selecting from a single sub-query. It will not support querying directly “FROM CoolaData”, nor from joined tables.

SELECT *
FROM (
  SELECT customer_user_id
  FROM CoolaData
  WHERE date_range(last 7 days)
)

Function: IN (sub_select)

To use the “IN” function with sub-select, please include a sub-select over the required data source in the “from” clause, and then apply the condition on it. See example:

SELECT customer_user_id
FROM (
    SELECT customer_user_id 
    FROM cooladata
    WHERE date_range(current month)
)
WHERE customer_user_id in (
    SELECT customer_user_id 
    FROM cooladata
    WHERE date_range(previous month) 
)

Function: CurrentCoolaDataUser

The special property “CurrentCoolaDataUser” can be used in a CQL query to automatically identify the current active CoolaData user. Once computed it will be replaced with the value of the current user’s email. For example, the following query will show all the article_ids that belong to the current active user:

SELECT article_id, count(*) views
FROM cooladata
WHERE date_range(current month) and email=CurrentCoolaDataUser
Print Friendly, PDF & Email

PIVOT function

The PIVOT function converts a result set from rows to columns, according to one of the with GROUP BY parameters values.

Syntax

PIVOT <aggregate_function>(<measure>) FOR <dimension> [HIGHEST <n> | IN (<values list>)]
  • PIVOT: 
    • PIVOT function should be added after GROUP BY / HAVING, and before ORDER BY / LIMIT.
    • Can only use one pivot function per query
    • Can only be used in the top query
    • Cannot be used with Union in the top query (in inner queries does)
    • Cannot be used in queries from Linked Data Sources.
    • Cannot be used with “SELECT * FROM cooladata”.
    • Can be used with ORDER BY with any dimension column, or column numbers (1, 2, …) – up to the number of columns in the original query.
  • aggregate_function:
    • Supports all basic aggregation functions: SUM, COUNT, MAX, MIN, etc.
    • Can only use one aggregate function.
    • Cannot be used with window functions.
  • measure: 

    • The measure can be any column (or “*” in case of count function) in the data source.
    • Can only use one measure.
  • dimension: 
    • The dimension can be any column in the data source.
    • This column will not be included in the result output, but instead, each value will be separated to a column. The column name will the be the value.
    • The pivot dimension should also be included in the GROUP BY clause of the top query (same as the query without pivot).
  • HIGHEST <n>: 
    • Optional – define the max number of value to return as columns.
    • n can be any number between 1-200.
    • Will return the top column according to the total value of the pivot aggregation function and measure.
    • When omitted, the query returns the top 200 values by default.
    • Cannot be used with “IN” parameter.
  • IN (<values list>): 
    • Optional – define the exact list of values to return as columns.
    • Values without results will not return as a column.
    • Other values will be ignored.
    • Cannot be used with “HIGHEST” parameter.

 

Examples

Basic query

SELECT ip_country, count(*) AS views
FROM cooladata 
WHERE date_range(context) AND filters(context) 
GROUP BY ip_country 
PIVOT SUM(views) FOR ip_country 
LIMIT 100

Results:

United States Israel Ireland
11,137 7,470 1,723

 

Using IN and multiple breakdown dimensions

SELECT day, ip_country, count(*) as views
FROM cooladata
WHERE date_range(context) AND filters(context) 
GROUP BY day, ip_country
PIVOT SUM(views) FOR ip_country IN ("Israel", "United States", "Ireland")
ORDER BY day
LIMIT 100
Results:
day United States Israel Ireland
2017-09-20 5,340 4,999 777
2017-09-21 2,554 1,402 355
2017-09-22 3,243 1,069 591
Print Friendly, PDF & Email