SQL When Expressions

An SQL When expression is a standard SQL Value Expression, and is coerced to a boolean type after evaluation. The WHEN clause is used to select values based on their timestamp. Only values with timestamps (accesed via the special value_timestamp() function) that cause the expression to evaluate true are kept; values resulting in evaluations of null or false are discarded.

Note that this syntax is not part of SQL, it is an MLDB extension, and is evaluated after the WHERE clause.

Using When and Where Clauses

The WHERE clause is evaluated before the WHEN clause. To illustrate that, consider the browse events dataset containing these values

One can select the events in the first five minutes of 2015 with this query

SELECT * FROM events 
WHEN value_timestamp() BETWEEN '2015-01-01T00:00:00' AND '2015-01-01T00:05:00'

The output is

Similarly, one can select users who viewed mldb.ai with this query

SELECT * FROM events 
WHERE view = 'mldb.ai'

Lastly, if we combined the WHEN and the WHERE clauses together

SELECT * FROM events 
WHEN value_timestamp() BETWEEN '2015-01-01T00:00:00' AND '2015-01-01T00:05:00' 
WHERE view = 'mldb.ai'

we get:

This is because the WHERE clause is evaluated prior to the WHEN clause.