Kinetica has native support for a variety of expressions, which are used as inputs while querying data (for supported SQL expressions, see SQL Support ). These native expressions can involve one or more constants (both numeric and string) and table columns; however, expressions cannot be applied to store-only columns. The expressions follow certain constraints based on where they are used, but all the expressions should follow the basic guidelines outlined below:
Important
Use parentheses liberally to ensure correct order-of-operations.
| Types | Details |
|---|---|
| String | String constants must be enclosed in single quotes or double quotes, e.g.,
|
| Numerical | Numerical constants can be expressed as:
|
Important
When these operators are applied to numeric columns, they will
interpret non-zero values as true and zero values as false,
returning 1 for true and 0 for false.
| Types | Details | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Mathematical | + - * / |
||||||||||||
| Bitwise | & | << >> ~ ^ |
||||||||||||
| Comparison |
|
||||||||||||
| Logical |
|
absmodgreatestleastsignexplnloglog10sqrtcbrtpowpowerldexpThis section comprises the following functions:
date type or time
type responsetimestamp type and/or
date typetimestamp type valuesNote
Integer fields are assumed to be seconds since the epoch; long/timestamp fields are assumed to be milliseconds since the epoch.
| Function | Details |
|---|---|
year(timestamp) |
Returns 4-digit year (A.D.) |
month(timestamp) |
Returns number of month [ 1 - 12 ] |
day(timestamp) |
Returns day of month [ 1 - 31 ] |
hour(timestamp) |
Returns hour of day [ 0 - 23 ] |
minute(timestamp) |
Returns minute of hour [ 0 - 59 ] |
sec(timestamp) |
Returns second of minute [ 0 - 59 ] |
second(timestamp) |
Synonymous with sec(timestamp) |
msec(timestamp) |
Returns millisecond of second [ 0 - 999 ] |
time(timestamp) |
Returns time (HH:MM:SS.mmm) of full timestamp |
quarter(timestamp) |
Returns quarter of year [ 1 - 4 ]; (1 = Jan, Feb, & Mar) |
week(timestamp) |
Returns week (or partial week) of year [ 1 - 53 ]; each full week starts on Sunday (1 = week containing Jan 1st) |
day_of_week(timestamp) |
Returns day of week [ 1 - 7 ], with 1 being Sunday |
dayofweek(timestamp) |
Synonymous with day_of_week(timestamp) |
dayofmonth(timestamp) |
Returns day of month [ 1 - 31 ] |
day_of_year(timestamp) |
Returns day of year [ 1 - 366 ] |
dayofyear(timestamp) |
Synonymous with day_of_year(timestamp) |
current_date() |
Returns the date as YYYY-MM-DD |
current_timestamp() |
Returns the time and date as the number of milliseconds since the epoch |
current_time() |
Returns the time as HH:MM:SS.mmm |
dayname(timestamp) |
Returns day name [ Sunday - Saturday ] |
monthname(timestamp) |
Returns month name [ January - December ] |
date(timestamp) |
Returns date in the format YYYY-MM-DD |
| Function | Details |
|---|---|
date_to_epoch_secs(year, month, day, hours, minutes, seconds) |
Converts the full date to seconds since the epoch. Negative values are
accepted (e.g., date_to_epoch_secs(2017,06,-15,09,22,15) would return
1494926535, which resolves to Tuesday, May 16, 2017 9:22:15 AM) |
date_to_epoch_msecs(year, month, day, hours, minutes, seconds, milliseconds) |
Converts the full date to milliseconds since the epoch. Negative values are accepted |
week_to_epoch_secs(year, week_number) |
Converts the year and week number to seconds since the epoch. Negative
values are accepted (e.g., week_to_epoch_secs(2017,-32) would return
1463270400, which resolves to Sunday, May 15, 2016 12:00:00 AM).
Each new week begins Sunday at midnight |
week_to_epoch_msecs(year, week_number) |
Converts the year and week number to seconds since the epoch. Negative values are accepted |
msecs_since_epoch(timestamp) |
Converts the timestamp to millseconds since the epoch |
timestamp_from_date_time(date, time) |
converts the date and time (as strings) to timestamp format, e.g.,
timestamp_from_date_time('2017-06-15', '10:37:30') would return
1497523050000, which resolves to
Thursday, June 15, 2017 10:37:30 AM. |
Note
The operated-on field must have timestamp annotation
| Function | Details |
|---|---|
string(timestamp) |
Converts
|
timestampadd(interval_type, interval_amount, timestamp) |
Adds the positive or negative
|
timestampdiff(unit_type, begin_timestamp, end_timestamp) |
Calculates the difference between two dates, returning the result in the units specified;
more precisely, how many
|
timeboundarydiff(unit_type, begin_timestamp, end_timestamp) |
Similarly to timestampdiff, this function calculates the difference between two dates
except timeboundarydiff is symmetric with timestampadd. This means the
timeboundarydiff in MONTH units between Mar 31st and Apr 30th is 1. |
| Function | Details |
|---|---|
dist(x1, y1, x2, y2) |
Computes the Euclidean distance, i.e. sqrt( (x1-x2)*(x1-x2) + (y1-y2)*(y1-y2) ) |
geodist(lon1, lat1, lon2, lat2) |
Computes the geographic great-circle distance (in meters) between two lat/lon points |
| Function | Details |
|---|---|
if(C,T,F) |
If
|
Important
Be mindful that no error is thrown when Kinetica tries to convert different data type in the Null functions below, so if the output is unexpected, it may be that the types used aren't of the same type.
| Function | Details |
|---|---|
IS_NULL(expr) |
Returns 1 (true) if expr is null; otherwise, returns 0
(false) |
NULLIF(expr_a, expr_b) |
Returns null if expr_a equals expr_b; otherwise, returns the
value of expr_a. Both expressions should be of the same convertible
data type |
NVL(expr_a, expr_b) |
Returns expr_a if it is not null; otherwise, returns expr_b.
Both expressions should be of the same convertible data type |
NVL2(expr, value_if_not_null, value_if_null) |
Evaluates expr: if expr does not return a null,
value_if_not_null is returned. If expr does return a null,
value_if_null is returned. Both value_if_not_null and
value_if_null should be of the same data type as expr or
implicitly convertible |
| Function | Details |
|---|---|
divz(a, b, c) |
Returns the quotient a / b unless b == 0, in which case it
returns c |
convert (original value, destination type) |
Returns the equivalent of original value converted to the
destination type. Useful for converting strings to numbers and
numbers to strings |
ceilceilingfloorroundImportant
These functions will only work with fixed-width string fields
(char1 - char256).
| Function | Details |
|---|---|
length(string) |
Number of characters in string |
left(string, length) |
The substring of size length consisting of the leftmost characters
in string |
right(string, length) |
The substring of size length consisting of the rightmost
characters in string |
ltrim(string) - string |
string with all leading whitespace characters removed |
rtrim(string) - string |
string with all trailing whitespace characters removed |
substring(string, start, length) |
Substring of string starting from position start (1-based),
containing up to length number of characters (fewer, if string
is shorter than start + length) |
concat(string1, string2) |
A concatenation of string1 and string2; use nested concat
calls to concatenate more than two strings:
concat(concat(cityName,':'),country) AS location. The resulting
field size of any concat will be a charN field big enough to hold
the concatenated fields. If the concatenated field widths total more
than 256 characters (the charN max length), the operation will fail. |
ascii(string) |
The ASCII code associated with of the first character |
char(ascii) |
The character represented by the standard ASCII code ascii
[ 0 - 127 ] |
locate(search string, string, [optional starting position]) |
The position of the first occurrence of search string in
string, starting the search at position 1, or optionally, at
optional starting position. |
replace(string, search string, replacement string) |
string with all occurrences of search string replaced by
replacement string (only fixed-width string fields,
char1 - char16) |
sincostancotasinacosatanatan2atn2degreesradianshypotsinhcoshtanhasinhacoshatanh| Function | Details |
|---|---|
truncate (value,number significant digits) |
For a given value, truncate to the specified number significant digits |
Many of the functions above accept expressions as inputs in place of column names for selecting data from tables e.g. /aggregate/minmax. Given below are some examples of column expressions:
(x + y)
(2 * col1) + col2
Data can be filtered with the use of filter expressions within the
/filter endpoint. These expressions may contain
column expressions as well as tests for equality/inequality for selecting
records from the database. A filter expression cannot contain
aggregation functions and should evaluate to a logical value
( true or false ). When the result of an expression evaluation is a
numerical value, the result is converted to a logical value as follows: 0
is considered false and any other value is considered as true. Some
examples of filter expressions are given below:
(x > y)
(a != b) or (c = d)
(timestamp > 1456749296789) and (x <= 10.0)
abs(timestamp - 1456749296789) < 60 * 60 * 1000
quarter(timestamp) = 1 and mod(year(timestamp), 4) = 0
msg_id == 'MSGID1'
Some endpoints accept aggregation expressions as inputs for selecting data from tables, e.g., /aggregate/groupby. Such expressions can only contain aggregation functions and non-nested functions of aggregation functions.
Available aggregation functions:
| Function | Details |
|---|---|
count(*) |
Count of all the values in a given column |
sum |
The sum of all values in a given column |
min |
The minimum value in a given column |
max |
The maximum value in a given column |
avg |
The average value in a given column |
mean |
Synonym for avg |
stddev |
The population standard deviation (i.e. the denominator is N) |
stddev_pop |
The population standard deviation (i.e. the denominator is N) |
stddev_samp |
The sample standard deviation (i.e. the denominator is N-1) |
var |
The population variance (i.e. the denominator is N) |
var_pop |
The population variance (i.e. the denominator is N) |
var_samp |
The sample variance (i.e. the denominator is N-1) |
arg_min |
The value of the second column where the first column is the minimum value (i.e. arg_min(x,y) returns the value of y in the
record holding the smallest value of x) |
arg_max |
The value of the second column where the first column is the maximum value (i.e. arg_min(x,y) returns the value of y in the
record the largest value of x) |
count_distinct |
Count of the distinct values in a given column |
Some examples of aggregate expressions:
sum(sale_price) - sum(base_price)
max(ceil(x)) - min(floor(x))
avg(abs(z - 100.0))