Expressions¶

Constants
Operators
Functions
Column Expressions
Filter Expressions
Aggregate Expressions

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.

Constants ¶

Types Details

String

String constants must be enclosed in single quotes or double quotes, e.g.,

'hello'
"world"

Numerical

Numerical constants can be expressed as:

decimal integers (31242)
hex integers (0x3420123a)
doubles (3.41e5)
floats (3.1415F)

Operators ¶

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

Bitwise & | << >> ~ ^

Comparison

> < >= <= == = != <> in

Only = and != are supported for unrestricted String fields
All comparison operators are supported for charN fields

Logical

Operator	Details
`and`	Both arguments are true
`or`	Either argument is true
`xor`	One argument is true, and one is false
`not`	The argument is false
`!`	Synonym for `not`

Mathematical + - * /

String Concatenation ||

Functions ¶

Conditional Functions ¶

Scalar Function Details

CASE(expr, {match_a, ..., match_N}, {value_a, ..., value_N}, value_if_no_match) Evaluates expr: returns the first value whose corresponding match is equal to expr; returns value_if_no_match if expr is not equal to any of match_a through match_N

IF(expr, value_if_true, value_if_false))

Evaluates expr: if true, returns value_if_true; otherwise, value_if_false

expr - any true/false condition. Note that when an integer column is used directly, this function will interpret non-zero values as true and zero values as false)
value_if_true - any numeric value
value_if_false - any numeric value

Conversion Functions ¶

Function	Details
`CAST(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
`CHAR(int)`	Returns the character associated with the ASCII code in `int`
`CHAR1(charN)`	Converts the given `charN` to `char1` type
`CHAR2(charN)`	Converts the given `charN` to `char2` type
`CHAR4(charN)`	Converts the given `charN` to `char4` type
`CHAR8(charN)`	Converts the given `charN` to `char8` type
`CHAR16(charN)`	Converts the given `charN` to `char16` type
`CHAR32(charN)`	Converts the given `charN` to `char32` type
`CHAR64(charN)`	Converts the given `charN` to `char64` type
`CHAR128(charN)`	Converts the given `charN` to `char128` type
`CHAR256(charN)`	Converts the given `charN` to `char256` type
`CONVERT(original value, destination type)`	Alias for `CAST`

Date, Time, and Timestamp Functions ¶

This section comprises the following functions:

Date/Time Functions, which can convert to or provide a date type or time type response
Timestamp/Date Conversion Functions, which can convert timestamp type and/or date type
Timestamp-only Functions, which can convert or manipulate timestamp type values

Date/Time Functions ¶

Note

Integer fields are assumed to be seconds since the epoch; long/timestamp fields are assumed to be milliseconds since the epoch.

Function	Details
`CURRENT_DATE()`	Returns the date as `YYYY-MM-DD`
`CURRENT_DATETIME()`	Returns the date & time as `YYYY-MM-DD HH24:MI:SS.mmm`
`CURRENT_TIME()`	Returns the time as `HH24:MI:SS.mmm`
`CURRENT_TIMESTAMP()`	Returns the date & time as the number of milliseconds since the epoch
`DATE(expr)`	Returns date in the format `YYYY-MM-DD`
`DATETIME(expr)`	Returns `expr` (as a string) as a datetime (`YYYY-MM-DD HH:MI:SS.mmm`)
`DAY(expr)`	Returns day of month [ `1` - `31` ]
`DAYNAME(expr)`	Returns day name [ `Sunday` - `Saturday` ]
`DAYOFMONTH(expr)`	Returns day of month [ `1` - `31` ]
`DAYOFWEEK(expr)`	Returns day of week [ `1` - `7` ], with `1` being Sunday
`DAY_OF_WEEK(expr)`	Synonymous with `DAY_OF_WEEK(expr)`
`DAYOFYEAR(expr)`	Returns day of year [ `1` - `366` ]
`DAY_OF_YEAR(expr)`	Synonymous with `DAYOFYEAR(timestamp)`
`HOUR(expr)`	Returns hour of day [ `0` - `23` ]
`MINUTE(expr)`	Returns minute of hour [ `0` - `59` ]
`MONTH(expr)`	Returns number of month [ `1` - `12` ]
`MONTHNAME(expr)`	Extracts the month of the year from `expr` and converts it to the corresponding month name [ `January` - `December`]
`MSEC(expr)`	Returns millisecond of second [ `0` - `999` ]
`QUARTER(expr)`	Returns quarter of year [ `1` - `4` ]; (`1` = Jan, Feb, & Mar)
`SEC(expr)`	Alias for `SECOND(expr)`
`SECOND(expr)`	Returns second of minute [ `0` - `59` ]
`TIME(expr)`	Returns time (`HH:MI:SS.mmm`) of full timestamp
`WEEK(timestamp)`	Returns week (or partial week) of year [ `1` - `53` ]; each full week starts on Sunday (`1` = week containing Jan 1st)
`YEAR(timestamp)`	Returns 4-digit year (A.D.)

Timestamp/Date Conversion Functions ¶

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.

Timestamp-only Functions ¶

Note

The operated-on field must have timestamp annotation

Function	Details
`STRING(timestamp)`	Converts `timestamp` to a string in `YYYY-MM-DD hh:mm:ss.mmm` format
`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.
`TIMESTAMPADD(interval_type, interval_amount, timestamp)`	Adds the positive or negative `interval_amount` of `interval_type` units to `timestamp` `interval_type` - valid values are: `YEAR` - year is modified by interval amount (not affected by leap year, etc.) `MONTH` - month is modified by interval amount and year adjusted if overflow/underflow occurs; day adjusted to last day of calculated month if not a valid day for that month, e.g., Apr 31st -> Apr 30th `DAY` - day is modified by interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs `HOUR` - hour is modified by interval amount (time not affected by daylight savings time, etc.); date is adjusted, if overflow/underflow occurs `MINUTE` - minute is modified by interval amount; hour & date are adjusted, if overflow/underflow occurs `SECOND` - second is modified by interval amount; minute, hour, & date are adjusted, if overflow/underflow occurs `MILLISECOND` - milliseconds are modified by interval amount; time & date are adjusted, if overflow/underflow occurs `QUARTER` - month is modified by three times the interval amount, irrespective of the number of days in the months in between; day adjusting performed the same way as in `MONTH` description, but only on final month, i.e. Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February `WEEK` - day is modified by 7 times the interval amount (time not affected by daylight savings time, etc.); month & year are adjusted, if overflow/underflow occurs `interval_amount` - positive (or negative) integer specifying how many `interval_type` units will be added (or subtracted) from `timestamp` `timestamp` - any field with a timestamp annotation
`TIMESTAMPDIFF(unit_type, begin_timestamp, end_timestamp)`	Calculates the difference between two dates, returning the result in the units specified; more precisely, how many `unit_type` units need to be added to or subtracted from `begin_timestamp` to equal `end_timestamp` (or get as close as possible without going past it) using the rules specified in `TIMESTAMPADD`. NOTE: this is not symmetric with `TIMESTAMPADD` in all cases, as adding 1 `MONTH` to Mar 31st results in Apr 30th, but the `TIMESTAMPDIFF` in `MONTH` units between those two dates is 0. `unit_type` - same tokens as specified for the `interval_type` of `TIMESTAMPADD` `begin_timestamp` - any field with a timestamp annotation `end_timestamp` - any field with a timestamp annotation

Geospatial Functions ¶

Enhanced Performance Functions ¶

The functions below all compare x and y coordinates to geometry objects (or vice versa), thus increasing their performance in queries. Each of these functions have a geometry-to-geometry version listed in the next section.

Function	Description
`STXY_CONTAINS(geom, x, y)`	Returns `1` (true) if `geom` contains the `x` and `y` coordinate, e.g. lies in the interior of `geom`. The coordinate cannot be on the boundary and also be contained because `geom` does not contain its boundary
`STXY_CONTAINSPROPERLY(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate intersects the interior of `geom` but not the boundary (or exterior) because `geom` does not contain its boundary but does contain itself
`STXY_COVEREDBY(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate is covered by `geom`
`STXY_COVERS(geom, x, y)`	Returns `1` (true) if `geom` covers the `x` and `y` coordinate
`STXY_DISJOINT(x, y, geom)`	Returns `1` (true) if the given `x` and `y` coordinate and the geometry `geom` do not spatially intersect.
`STXY_DISTANCE(x, y, geom)`	Returns the minimum 2-D Cartesian distance (in degrees) between the given `x` and `y` coordinate and `geom`.
`STXY_DWITHIN(x, y, geom, distance)`	Returns `1` (true) if the `x` and `y` coordinate is within the specified `distance` from `geom`.
`STXY_ENVDWITHIN(x, y, geom, distance)`	Returns `1` (true) if the `x` and `y` coordinate is within the specified `distance` from the bounding box of `geom`.
`STXY_ENVINTERSECTS(x, y, geom)`	Returns `1` (true) if the bounding box of the given geometry `geom` intersects the `x` and `y` coordinate.
`STXY_INTERSECTION(x, y, geom)`	Returns the shared portion between the `x` and `y` coordinate and the given geometry `geom`, i.e. the point itself.
`STXY_INTERSECTS(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate and `geom` intersect in 2-D.
`STXY_TOUCHES(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate and geometry `geom` have at least one point in common but their interiors do not intersect.
`STXY_WITHIN(x, y, geom)`	Returns `1` (true) if the `x` and `y` coordinate is completely inside the `geom` geometry i.e., not on the boundary

Geometry Functions ¶

Function	Details
`DIST(x1, y1, x2, y2)`	Computes the Euclidean distance (in degrees), 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.
`ST_ADDPOINT(linestring, point, position)`	Adds a the given `point` geometry to the given `linestring` geometry at the specified `position`, which is a 0-based index.
`ST_ALMOSTEQUALS(geom1, geom2, decimal)`	Returns `1` (true) if given geometries, `geom1` and `geom2`, are almost spatially equal within the given amount of `decimal` scale. Note that geometries will still be considered equal if the decimal scale for the geometries is within a half order of magnitude of each other, e.g, if `decimal` is set to 2, then POINT(63.4 123.45) and POINT(63.4 123.454) are equal, but POINT(63.4 123.45) and POINT(63.4 123.459) are not equal. The geometry types must match to be considered equal.
`ST_AREA(geom)`	Returns the area of the given geometry `geom` (in degrees) if it is a POLYGON or MULTIPOLYGON. Returns `0` if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING.
`ST_BOUNDARY(geom)`	Returns the closure of the combinatorial boundary of a given geometry `geom`. Returns an empty geometry if `geom` is an empty geometry. Returns a `null` if `geom` is a GEOMETRYCOLLECTION
`ST_BOUNDINGDIAGONAL(geom)`	Returns the diagonal of the given geometry's (`geom`) bounding box.
`ST_BUFFER(geom, radius, style)`	Returns a geometry that represents all points whose distance from the given geometry `geom` is less than or equal to the given distance `radius` in degrees. Additional `style` parameters can be specified as a list of blank-separated key-value pairs, e.g., `'quad_segs=8 endcap=round'` : `quad_segs` -- the number of segments used to approximate a quarter circle (default is `8`) `endcap` -- the endcap style of the buffer (default is `round`); options are `round`, `flat` (or `butt`), and `square` `join` -- the join style of the buffer (default is `round`); options are `round`, `mitre` (or `miter`), and `bevel` `mitre_limit` -- the mitre ratio limit expressed as a floating point number (`miter_limit` is also acceptable)
`ST_CENTROID(geom)`	Calculates the center of the given geometry `geom` as a POINT. For (MULTI)POINTs, the center is calculated as the average of the input coordinates. For (MULTI)LINESTRINGs, the center is calculated as the weighted length of each given LINESTRING. For (MULTI)POLYGONs, the center is calculated as the weighted area of each given POLYGON. If `geom` is an empty geometry, an empty GEOMETRYCOLLECTION is returned
`ST_CLIP(geom1, geom2)`	Returns the geometry shared between given geometries `geom1` and `geom2`.
`ST_COLLECTIONEXTRACT(collection, type)`	Returns only the specified `type` from the given geometry `collection`. Type is a number that maps to the following: `1` = `POINT` `2` = `LINESTRING` `3` = `POLYGON`
`ST_COLLECTIONHOMOGENIZE(collection)`	Returns the simplest form of the given `collection`, e.g., a collection with a single POINT will be returned as `POINT(x y)`, and a collection with multiple individual points will be returned as a MULTIPOINT.
`ST_CONTAINS(geom1, geom2)`	Returns `1` (true) if no points of `geom2` lie in the exterior of `geom1` and at least one point of `geom2` lies in the interior of `geom1`. Note that `geom1` does not contain its boundary but does contain itself.
`ST_CONTAINSPROPERLY(geom1, geom2)`	Returns `1` (true) if `geom2` intersects the interior of `geom1` but not the boundary (or exterior). Note that `geom1` does not contain its boundary but does contain itself.
`ST_CONVEXHULL(geom)`	Returns the minimum convex geometry that encloses all geometries in the given `geom` set.
`ST_COORDDIM(geom)`	Returns the coordinate dimension of the given `geom`, e.g., a geometry with `x`, `y`, and `z` coordinates would return `3`.
`ST_COVEREDBY(geom1, geom2)`	Returns `1` (true) if no point in `geom1` is outside `geom2`.
`ST_COVERS(geom1, geom2)`	Returns `1` (true) if no point in `geom2` is outside `geom1`.
`ST_CROSSES(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, spatially cross, meaning some but not all interior points in common.
`ST_DIFFERENCE(geom1, geom2)`	Returns a geometry that represents the part of `geom1` that does not intersect with `geom2`.
`ST_DIMENSION(geom)`	Returns the dimension of the given geometry `geom`, which is less than or equal to the coordinate dimension. If `geom` is a single geometry, a `0` is for `POINT`, a `1` is for `LINESTRING`, and a `2` is for `POLYGON`. If `geom` is a collection, it will return the largest dimension from the collection. If `geom` is empty, `0` is returned.
`ST_DISJOINT(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, do not spatially intersect.
`ST_DISTANCE(geom1, geom2)`	Returns the minimum 2-D Cartesian distance (in degrees) between the given geometries, `geom1` and `geom2`.
`ST_DWITHIN(geom1, geom2, distance)`	Returns `1` (true) if the geometries `geom1` and `geom2` are within the specified `distance` of each other.
`ST_ELLIPSE(centerx, centery, height, width)`	Returns an ellipse using the following values: `centerx` -- the x coordinate or longitude used to center the ellipse `centery` -- the y coordinate or latitude used to center the ellipse `height` -- the height of the ellipse (in degrees) `width` -- the width of the ellipse (in degrees)
`ST_ENDPOINT(geom)`	Returns the last point of the given `geom` as a POINT if it's a LINESTRING. If `geom` is not a a LINESTRING, `null` is returned.
`ST_ENVDWITHIN(geom1, geom2, distance)`	Returns `1` (true) if `geom1` is within the specified `distance` of the bounding box of `geom2`.
`ST_ENVELOPE(geom)`	Returns the bounding box of a given geometry `geom`.
`ST_ENVINTERSECTS(geom1, geom2)`	Returns `1` (true) if the bounding box of the given geometries, `geom1` and `geom2`, intersect.
`ST_EQUALS(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, are spatially equal. Note that order does not matter.
`ST_EQUALSEXACT(geom1, geom2, tolerance)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, are almost spatially equal within some given `tolerance`. If the values within the given geometries are within the `tolerance` value of each other, they're considered equal, e.g., if `tolerance` is 2, POINT(1 1) and POINT(1 3) are considered equal, but POINT(1 1) and POINT(1 3.1) are not. Note that the geometry types have to match for them to be considered equal.
`ST_ERASE(geom1, geom2)`	Returns the result of erasing a portion of `geom1` equal to the size of `geom2`.
`ST_EXPAND(geom, units)`	Returns the bounding box expanded in all directions by the given `units` of the given `geom`. The expansion can also be defined for separate directions by providing separate parameters for each direction, e.g., `ST_EXPAND(geom, unitsx, unitsy, unitsz, unitsm)`.
`ST_EXPANDBYRATE(geom, rate)`	Returns the bounding box expanded by a given `rate` (a ratio of width and height) for the given geometry `geom`. The `rate` must be between 0 and 1.
`ST_EXTERIORRING(geom)`	Returns a LINESTRING representing the exterior ring of the given POLYGON `geom`
`ST_GENERATEPOINTS(geom, num)`	Creates a MULTIPOINT containing a number `num` of randomly generated points within the boundary of `geom`.
`ST_GEOMETRYN(geom, index)`	Returns the `index` geometry back from the given `geom` geometry. The `index` starts from 1 to the number of geometry in `geom`.
`ST_GEOMETRYTYPE(geom)`	Returns the type of geometry from the given `geom`.
`ST_GEOMETRYTYPEID(geom)`	Returns the type ID of from `geom`. Type and ID mappings: POINT = 0 LINESTRING = 1 LINEARRING = 2 POLYGON = 3 MULTIPOINT = 4 MULTILINESTRING = 5 MULTIPOLYGON = 6 GEOMETRYCOLLECTION = 7
`ST_GEOMFROMTEXT(wkt)`	Returns a geometry from the given Well-Known text representation `wkt`. Note that this function is only compatible with constants.
`ST_INTERIORRINGN(geom, n)`	Returns the `n`-th interior LINESTRING ring of the POLYGON `geom`. If `geom` is not a POLYGON or the given `n` is out of range, a `null` is returned. The index begins at 1.
`ST_INTERSECTION(geom1, geom2)`	Returns the shared portion between given geometries `geom1` and `geom2`.
`ST_INTERSECTS(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, intersect in 2-D.
`ST_ISCLOSED(geom)`	Returns `1` (true) if the given geometry's (`geom`) start and end points coincide.
`ST_ISCOLLECTION(geom)`	Returns `1` (true) if `geom` is a collection, e.g., GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, etc.
`ST_ISEMPTY(geom)`	Returns `1` (true) if `geom` is empty.
`ST_ISRING(geom)`	Returns `1` (true) if LINESTRING `geom` is both closed (per `ST_ISCLOSED`) and "simple" (per `ST_ISSIMPLE`). Returns `0` if `geom` is not a LINESTRING.
`ST_ISSIMPLE(geom)`	Returns `1` (true) if `geom` has no anomalous geometric points, e.g., self-intersection or self-tangency.
`ST_ISVALID(geom)`	Returns `1` (true) if `geom` (typically a [MULTI]POLYGON) is well formed. A POLYGON is valid if its rings do not cross and its boundary intersects only at POINTs (not along a line). The POLYGON must also not have dangling LINESTRINGs. A MULTIPOLYGON is valid if all of its elements are also valid and the interior rings of those elements do not intersect. Each element's boundaries may touch but only at POINTs (not along a line).
`ST_LENGTH(geom)`	Returns the 2-D length of the geometry (in degrees) if it is a LINESTRING or MULTILINESTRING. Returns `0` if another type of geometry, e.g., POINT, MULTIPOINT, etc.
`ST_LINEFROMMULTIPOINT(geom)`	Creates a LINESTRING from `geom` if it is a MULTIPOINT. Returns `null` if `geom` is not a MULTIPOINT.
`ST_MAKEENVELOPE(xmin, ymin, xmax, ymax)`	Creates a rectangular POLYGON from the given min and max parameters.
`ST_MAKELINE(geom[, geom2, geom3])`	Creates a LINESTRING from `geom` if it is a MULTIPOINT. If `geom` is a POINT, there must be at least one other POINT to construct a LINESTRING. If `geom` is a LINESTRING, it must have at least two points. Returns `null` if `geom` is not a POINT, MULTIPOINT, or LINESTRING. Note: This function can be rather costly in terms of performance.
`ST_MAKEPOINT(x, y)`	Creates a POINT at the given coordinate. Note: This function can be rather costly in terms of performance.
`ST_MAKEPOLYGON(geom)`	Creates a POLYGON from `geom`. Inputs must be closed LINESTRINGs. Note: This function can be rather costly in terms of performance.
`ST_MAXX(geom)`	Returns the maximum x coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MAXY(geom)`	Returns the maximum y coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MAXZ(geom)`	Returns the maximum z coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MINX(geom)`	Returns the minimum x coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MINY(geom)`	Returns the minimum y coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MINZ(geom)`	Returns the minimum z coordinate of a bounding box for the given `geom` geometry. This function works for 2-D and 3-D geometries.
`ST_MULTI(geom)`	Returns `geom` as a MULTI- geometry, e.g., a POINT would return a MULTIPOINT.
`ST_MULTIPLERINGBUFFERS(geom, distance, outside)`	Creates multiple buffers at specified `distance` around the given `geom` geometry. Multiple distances are specified as comma-separated values in an array, e.g., `[10,20,30]`. Valid values for `outside` are: `FULL` -- indicates that buffers will overlap or cover the given `geom` geometry. This is the default. `OUTSIDE_ONLY` -- indicates that buffers will be rings around the given `geom` geometry.
`ST_NORMALIZE(geom)`	Returns `geom` in its normalized (canonical) form, which may rearrange the points in lexicographical order.
`ST_NPOINTS(geom)`	Returns the number of points (vertices) in `geom`.
`ST_NUMGEOMETRIES(geom)`	If `geom` is a collection or MULTI- geometry, returns the number of geometries. If `geom` is a single geometry, returns 1.
`ST_NUMINTERIORRINGS(geom)`	Returns the number of interior rings if `geom` is a POLYGON. Returns `null` if `geom` is anything else.
`ST_NUMPOINTS(geom)`	Returns the number of points in the `geom` LINESTRING. Returns `null` if `geom` is not a LINESTRING.
`ST_OVERLAPS(geom1, geom2)`	Returns `1` (true) if given geometries `geom1` and `geom2` share space.
`ST_POINT(x, y)`	Returns a POINT with the given `x` and `y` coordinates.
`ST_POINTN(geom, n)`	Returns the `n`-th point in LINESTRING `geom`. Negative values are valid, but note that they are counted backwards from the end of `geom`. A `null` is returned if `geom` is not a LINESTRING.
`ST_POINTS(geom)`	Returns a MULTIPOINT containing all of the coordinates of `geom`.
`ST_REMOVEPOINT(geom, offset)`	Remove a point from LINESTRING `geom`. Index starts at 0, but you can use `offset` to make point selection easier.
`ST_REMOVEREPEATEDPOINTS(geom, tolerance)`	Removes points from `geom` if the point's vertices are greater than or equal to the `tolerance` of the previous point in the geometry's list. If `geom` is not a MULTIPOINT, MULTILINESTRING, or a MULTIPOLYGON, no points will be removed.
`ST_REVERSE(geom)`	Return the geometry with its coordinate order reversed.
`ST_SCALE(geom, x, y)`	Scales `geom` by multiplying its respective vertices by the given `x` and `y` values. This function also supports scaling `geom` using another geometry object, e.g., `ST_SCALE('POINT(3 4)', 'POINT(5 6)')` would return `POINT(15 24)`. If specifying `x` and `y` for scale, note that the default value is `0`, e.g., `ST_SCALE('POINT(1 3)', 4)` would return `POINT(4 0)`.
`ST_SETPOINT(geom1, position, geom2)`	Replace a point of LINESTRING `geom1` with POINT `geom2` at `position` (base 0). Negative values are valid, but note that they are counted backwards from the end of `geom`.
`ST_SHAREDPATH(geom1, geom2)`	Returns a collection containing paths shared by `geom1` and `geom2`.
`ST_SNAP(geom1, geom2, tolerance)`	Snaps `geom1` to `geom2` within the given `tolerance`. If the `tolerance` causes `geom1` to not snap, the geometries will be returned unchanged.
`ST_SPLIT(geom1, geom2)`	Returns a collection of geometries resulting from the split between `geom1` and `geom2` geometries.
`ST_STARTPOINT(geom)`	Returns the first point of LINESTRING `geom` as a POINT. Returns `null` if `geom` is not a LINESTRING.
`ST_SYMDIFFERENCE(geom1, geom2)`	Returns a geometry that represents the portions of `geom1` and `geom2` geometries that do not intersect.
`ST_TOUCHES(geom1, geom2)`	Returns `1` (true) if the given geometries, `geom1` and `geom2`, have at least one point in common but their interiors do not intersect.
`ST_TRANSLATE(geom, deltax, deltay)`	Translate `geom` by given offsets `deltax` and `deltay`. A z-coordinate offset can be applied using `deltaz`.
`ST_UNION(geom1, geom2)`	Returns a geometry that represents the point set union of the two given geometries, `geom1` and `geom2`.
`ST_UNIONCOLLECTION(geom)`	Returns a geometry that represents the point set union of a single given geometry `geom`.
`ST_UPDATE(geom1, geom2)`	Returns a geometry that is `geom1` geometry updated by `geom2` geometry
`ST_WITHIN(geom1, geom2)`	Returns `1` (true) if the `geom1` geometry is inside the `geom2` geometry. Note that as long as at least one point is inside of `geom2`, `geom1` is considered within `geom2` even if the rest of the `geom1` lies along the boundary of `geom2`
`ST_X(geom)`	Returns the X coordinate of the POINT `geom`; if the coordinate is not available, `null` is returned. `geom` must be a POINT.
`ST_Y(geom)`	Returns the Y coordinate of the POINT `geom`; if the coordinate is not available, `null` is returned. `geom` must be a POINT.

Geospatial Aggregation Functions ¶

Function	Details
`ST_DISSOLVE(geom)`	Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group.

Math Functions ¶

Function	Details
`ABS(expr)`	Returns the absolute value of `expr`
`ACOS(expr)`	Calculates the inverse cosine (arccosine) of `expr`
`ACOSH(expr)`	Returns the inverse hyperbolic cosine of `expr`
`ASIN(expr)`	Calculates the inverse sine (arcsine) of `expr`
`ASINH(expr)`	Returns the inverse hyperbolic sine of `expr`
`ATAN(expr)`	Calculates the inverse tangent (arctangent) of `expr`
`ATANH(expr)`	Returns the inverse hyperbolic tangent of `expr`
`ATAN2(x, y)`	Calculates the inverse tangent (arctangent) using two arguments
`ATN2(x, y)`	Synonymous with `ATAN2(x, y)`
`CBRT(expr)`	Calculates the cube root of `expr`
`CEIL(expr)`	Alias for `CEILING`
`CEILING(expr)`	Rounds `expr` up to the next highest integer
`COS(expr)`	Calculates the cosine of `expr`
`COSH(expr)`	Returns the hyperbolic cosine of `expr`
`COT(expr)`	Calculates the cotangent of `expr`
`DEGREES(expr)`	Converts `expr` (in radians) to degrees
`DIVZ(a, b, c)`	Returns the quotient `a / b` unless `b == 0`, in which case it returns `c`
`EXP(expr)`	Raises e to the power of `expr`
`FLOOR(expr)`	Rounds `expr` down to the next lowest integer
`GREATEST(expr_a, ..., expr_N)`	Returns whichever of `expr_a` through `expr_N` has the largest value, based on typed comparison
`HYPOT(x, y)`	Calculates the hypotenuse of `x` and `y`
`ISNAN(expr)`	Returns `1` (true) if `expr` is not a number by IEEE standard; otherwise, returns `0` (false)
`IS_NAN(expr)`	Synonymous with `ISNAN(expr)`
`ISINFINITY(expr)`	Returns `1` (true) if `expr` is infinity by IEEE standard; otherwise, returns `0` (false)
`IS_INFINITY(expr)`	Synonymous with `ISINFINITY(expr)`
`LDEXP(x, exp)`	Returns the value of `x` * 2^``exp``
`LEAST(expr_a, ..., expr_N)`	Returns whichever of `expr_a` through `expr_N` has the smallest value, based on typed comparison
`LN(expr)`	Returns the natural logarithm of `expr`
`LOG(expr)`	Synonymous with `LN(expr)`
`LOG10(expr)`	Calculates the base-10 logarithm of `expr`
`MOD(dividend, divisor)`	Calculates the remainder after integer division of `dividend` by `divisor`
`POW(base, exponent)`	Alias for `POWER`
`POWER(base, exponent)`	Calculates `base` raised to the power of `exponent`
`RADIANS(expr)`	Converts `expr` (in degrees) to radians
`ROUND(expr, scale)`	Rounds `expr` to the nearest decimal number with `scale` decimal places when `scale` is a positive number; rounds to the nearest number such that the result has `-(scale)` zeros to the left of the decimal point when `scale` is negative; use `scale` of 0 to round to the nearest integer. Examples: `ROUND(12345.678, 2) --> 12345.68` `ROUND(12345.678, 0) --> 12345` `ROUND(45678.900, -2) --> 45700`
`SIGN(expr)`	Determines whether a number is positive, negative, or zero; returns one of the following three values: positive: `1` zero: `0` negative: `-1`
`SIN(expr)`	Calculates the sine of `expr`
`SINH(expr)`	Returns the hyperbolic sine of `expr`
`SQRT(expr)`	Calculates the square root of `expr`
`TAN(expr)`	Calculates the tangent of `expr`
`TANH(expr)`	Returns the hyperbolic tangent of `expr`
`TRUNCATE(expr, scale)`	Rounds `expr` down to the nearest decimal number with `scale` decimal places, following the same rules as `ROUND`. Examples: `TRUNCATE(12345.678, 2) --> 12345.67` `TRUNCATE(12345.678, 0) --> 12345` `TRUNCATE(45678.900, -2) --> 45600`

Null Functions ¶

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

String Functions ¶

Important

These functions will only work with fixed-width string fields (char1 - char256).

Function	Details
`ASCII(string)`	The ASCII code associated with of the first character
`CHAR(ascii)`	The character represented by the standard ASCII code `ascii` in the range [ `0` - `127` ]
`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.
`CONCAT_TRUNCATE(string1, string2)`	A concatenation of `string1` and `string2` that truncates `string2` to fit within the size of `string1`, e.g., `CONCAT_TRUNCATE('ABC123','DEFG')` would return `ABC123DE` because `ABC123` is treated as a char8 (rounding up from 6 characters).
`CONTAINS(pattern, string)`	Returns whether `string` contains the string-literal `pattern`
`ENDS_WITH(pattern, string)`	Returns whether `string` ends with the string-literal `pattern`
`LCASE(expr)`	Converts `expr` to lowercase
`LEFT(string, length)`	The substring of size `length` consisting of the leftmost characters in `string`
`LENGTH(string)`	Number of characters in `string`
`LIKE(string, pattern)`	Returns whether `string` matches the given `pattern`. The match is a string literal one with the following exceptions: `%` matches any string of 0 or more characters `_` matches any single character
`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`.
`LOWER(expr)`	Alias for `LCASE`
`LTRIM(string)`	`string` with all leading whitespace characters removed
`POSITION(match_expr, ref_expr, [start_pos])`	Returns the starting position of the first match of `match_expr` in `ref_expr`, starting from position 1 or `start_pos` (if specified)
`REPLACE(string, search string, replacement string)`	`string` with all occurrences of `search string` replaced by `replacement string` (only fixed-width string fields, `char1` - `char16`)
`RIGHT(string, length)`	The substring of size `length` consisting of the rightmost characters in `string`
`RTRIM(string)`	`string` with all trailing whitespace characters removed
`STARTS_WITH(pattern, string)`	Returns whether `string` starts with the string-literal `pattern`
`SUBSTR(expr, start_pos, num_chars)`	Alias for `SUBSTRING`
`SUBSTRING(expr, start_pos, num_chars)`	Substring of `string` starting from position `start` (1-based), containing up to `length` number of characters (fewer, if `string` is shorter than start + length)
`TRIM(expr)`	Removes whitespace from both sides of `expr`
`UCASE(expr)`	Converts `expr` to uppercase
`UPPER(expr)`	Alias for `UCASE`

Column Expressions ¶

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

Filter Expressions ¶

Data can be filtered with the use of filter expressions within many endpoints; e.g., /filter. 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'
(x = 5) and y in (10,20,30)

Aggregate Expressions ¶

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.

Function	Details
`COUNT(expr)`	Count of non-null values of `expr`; use `*` to count all values within an aggregation group or over an entire table
`SUM(expr)`	The sum of values of `expr`
`MIN(expr)`	The minimum of values of `expr`
`MAX(expr)`	The maximum of values of `expr`
`AVG(expr)`	The average of values of `expr`
`MEAN(expr)`	Synonym for `AVG`
`STDDEV(expr)`	The population standard deviation over values of `expr` (i.e. the denominator is N)
`STDDEV_POP(expr)`	The population standard deviation over values of `expr` (i.e. the denominator is N)
`STDDEV_SAMP(expr)`	The sample standard deviation over values of `expr` (i.e. the denominator is N-1)
`VAR(expr)`	The population variance over values of `expr` (i.e. the denominator is N)
`VAR_POP(expr)`	The population variance over values of `expr` (i.e. the denominator is N)
`VAR_SAMP(expr)`	The sample variance over values of `expr` (i.e. the denominator is N-1)
`ARG_MIN(agg_expr, ret_expr)`	The value of `ret_expr` where `agg_expr` is the minimum value (e.g. `ARG_MIN(cost, product_id)` returns the product ID of the lowest cost product)
`ARG_MAX(agg_expr, ret_expr)`	The value of `ret_expr` where `agg_expr` is the maximum value (e.g. `ARG_MAX(cost, product_id)` returns the product ID of the highest cost product)
`COUNT_DISTINCT(expr)`	Count of the distinct values of `expr`

Some examples of aggregate expressions:

SUM(sale_price) - SUM(base_price)
MAX(CEIL(x)) - MIN(FLOOR(x))
AVG(ABS(z - 100.0))

Table Of Contents

Expressions¶