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 storeonly 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 orderofoperations.
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 nonzero values as true and zero values as false,
returning 1
for true and 0
for false.
Types  Details  

Bitwise  &  << >> ~ ^ 

Comparison 


Logical 


Mathematical  +  * / 

String Concatenation   
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

For the CAST()
and CONVERT()
functions, valid destination types are:
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, style) 
Returns the equivalent of original value converted to the destination type . The
style parameter is currently only applicable when the destination type is
string and the original value is of timestamp or datetime type. The
supported style values are 0 (mon dd yyyy hh:mi AM or PM ) or 21 /
121 (yyymmdd hh:mi:ss.mmm ) 
This 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 

CURRENT_DATE() 
Returns the date as YYYYMMDD 
CURRENT_DATETIME() 
Returns the date & time as YYYYMMDD 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 YYYYMMDD 
DATETIME(expr) 
Returns expr (as a string) as a datetime (YYYYMMDD 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 ] 
NOW() 
Alias for CURRENT_DATETIME() 
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 4digit year (A.D.) 
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('20170615', '10:37:30') would return
1497523050000 , which resolves to
Thursday, June 15, 2017 10:37:30 AM. 
Note
The operatedon field must have timestamp annotation
Function  Details 

STRING(timestamp) 
Converts timestamp to a string in YYYYMMDD 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

TIMESTAMPDIFF(unit_type, begin_timestamp, end_timestamp) 
Calculates the difference between two dates, returning the result in the units specified;
more precisely, how many

Important
Results for invalid geometry objects cannot be guaranteed to be consistent
with the described behavior of a function. It's recommended that
ST_ISVALID
be used to determine if a geometry is valid before any
additional calculations are made
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 geometrytogeometry 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(geom, x, y) 
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, solution) 
Calculates the minimum distance between the given
Note: Solution types 
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 2D. 
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. If geom is a GEOMETRYCOLLECTION, a 0 is
returned regardless if the point and geometry touch 
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 
Function  Details 

DIST(x1, y1, x2, y2) 
Computes the Euclidean distance (in degrees), i.e. SQRT( (x1x2)*(x1x2) + (y1y2)*(y1y2) ) . 
GEODIST(lon1, lat1, lon2, lat2) 
Computes the geographic greatcircle 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 0based 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

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_CLOSESTPOINT(geom1, geom2, solution) 
Calculates the 2D
Note: Solution type 
ST_COLLECT(geom1, geom2) 
Returns a MULTI* or GEOMETRYCOLLECTION comprising geom1 and geom2 . If geom1 and geom2
are the same, singular geometry type, a MULTI* is returned, e.g., if geom1 and geom2 are both
POINTs (empty or no), a MULTIPOINT is returned. If geom1 and geom2 are neither the same type
nor singular geometries, a GEOMETRYCOLLECTION is returned. 
ST_COLLECTIONEXTRACT(collection, type) 
Returns only the specified

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. If geom1 and/or geom2 are a GEOMETRYCOLLECTION, a
0 is returned regardless if the two geometries cross 
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, solution) 
Calculates the minimum distance between the given geometries,
Note: Solution types 
ST_DISTANCEPOINTS(x1, y1, x2, y2, solution) 
Calculates the minimum distance between the given points,

ST_DFULLYWITHIN(geom1, geom2, distance) 
Returns 1 (true) if the geometries geom1 and geom2 are fully within the specified
2D Euclidean distance of each other 
ST_DWITHIN(geom1, geom2, distance) 
Returns 1 (true) if the geometries geom1 and geom2 are at least partially within the
specified distance of each other 
ST_ELLIPSE(centerx, centery, height, width) 
Returns an ellipse using the following values:

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

ST_GEOMFROMTEXT(wkt) 
Returns a geometry from the given WellKnown 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 2D 
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., selfintersection or
selftangency 
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 2D 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_LINEINTERPOLATEPOINT(geom, fraction) 
Returns a POINT that represents the specified fraction of the LINESTRING geom . If geom is
either empty or not a LINESTRING, null is returned 
ST_LONGESTLINE(geom1, geom2) 
Returns the 2D LINESTRING that represents the longest line of points between the two geometries. If
multiple longest lines are found, only the first line found is returned. If geom1 or geom2 is
empty, null is returned 
ST_MAKEENVELOPE(xmin, ymin, xmax, ymax) 
Creates a rectangular POLYGON from the given min and max parameters 
ST_MAKELINE(geom[, geom2]) 
Creates a LINESTRING from 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 Note: This function can be rather costly in terms of performance 
ST_MAKETRIANGLE2D(x1, y1, x2, y2, x3, y3) 
Creates a closed 2D POLYGON with three vertices 
ST_MAKETRIANGLE3D(x1, y1, z1, x2, y2, z2,
x3, y3, z3) 
Creates a closed 3D POLYGON with three vertices 
ST_MAXDISTANCE(geom1, geom2) 
Returns the maximum 2D Euclidean distance between the given geom1 and geom2 geometries. If
geom1 or geom2 are empty, null is returned 
ST_MAXX(geom) 
Returns the maximum x coordinate of a bounding box for the given geom geometry. This function
works for 2D and 3D geometries. 
ST_MAXY(geom) 
Returns the maximum y coordinate of a bounding box for the given geom geometry. This function
works for 2D and 3D geometries. 
ST_MAXZ(geom) 
Returns the maximum z coordinate of a bounding box for the given geom geometry. This function
works for 2D and 3D geometries. 
ST_MINX(geom) 
Returns the minimum x coordinate of a bounding box for the given geom geometry. This function
works for 2D and 3D geometries. 
ST_MINY(geom) 
Returns the minimum y coordinate of a bounding box for the given geom geometry. This function
works for 2D and 3D geometries. 
ST_MINZ(geom) 
Returns the minimum z coordinate of a bounding box for the given geom geometry. This function
works for 2D and 3D 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

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. If geom1 and/or
geom2 are a GEOMETRYCOLLECTION, a 0 is returned regardless if the two geometries overlap 
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_SEGMENTIZE(geom, max_segment_length) 
Returns the given geom but segmentized n number of times based on how the
max_segment_length distance (in meters) divides up the original geometry. The new geom is
guaranteed to have segments that are smaller than the given max_segment_length . Note that POINTs
are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING,
MULTIPOINT, etc.) can segmentized, but only the individual parts will be segmentized, not the
collection as a whole 
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_SHORTESTLINE(geom1, geom2) 
Returns the 2D LINESTRING that represents the shortest line of points between the two geometries. If
multiple shortest lines are found, only the first line found is returned. If geom1 or geom2
is empty, null is returned 
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. If geom1 and/or geom2 are a GEOMETRYCOLLECTION,
a 0 is returned regardless if the two geometries touch 
ST_TRANSLATE(geom, deltax, deltay[, deltaz]) 
Translate geom by given offsets deltax and deltay . A zcoordinate 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. 
Function  Details 

ST_AGGREGATE_COLLECT(geom) 
Alias for ST_COLLECT_AGGREGATE() 
ST_COLLECT_AGGREGATE(geom) 
Returns a GEOMETRYCOLLECTION comprising all geometries found in geom . Any MULTI* geometries will be divided
into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and
POINT(1 1) in the results; the same is true for elements of a GEOMETRYCOLLECTION found in geom . Any empty
geometries in geom are ignored even if they are part of a GEOMETRYCOLLECTION. 
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. 
ST_LINESTRINGFROMORDEREDPOINT(x, y, t) 
Returns a LINESTRING that represents a "track" of the given points (x , y ) ordered by the given sort
column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are
null , the null "point" will be left out of the resulting LINESTRING. If there's only one nonnull "point"
in the source table, a POINT is returned. If there are no nonnull "points" in the source table, a null is
returned 
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 base10 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

SIGN(expr) 
Determines whether a number is positive, negative, or zero; returns one of the following three values:

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

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 
Important
These functions will only work with fixedwidth 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 stringliteral pattern 
ENDS_WITH(pattern, string) 
Returns whether string ends with the stringliteral 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

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 fixedwidth 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 stringliteral pattern 
SUBSTR(expr, start_pos, num_chars) 
Alias for SUBSTRING 
SUBSTRING(expr, start_pos, num_chars) 
Substring of string starting from position start (1based),
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 
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 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)
Some endpoints accept aggregation expressions as inputs for selecting data from tables, e.g., /aggregate/groupby. Such expressions can only contain aggregation functions and nonnested functions of aggregation functions.
Function  Details 

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) 
AVG(expr) 
The average of values of expr 
CORR(expr1, expr2) 
Calculates the correlation coefficient of expr1 and expr2 
CORRELATION(expr1, expr2) 
Alias for CORR 
CORRCOEF(expr1, expr2) 
Alias for CORR 
COUNT(expr) 
Count of nonnull values of expr ; use * to count all values within an aggregation group or over an entire table 
COUNT_DISTINCT(expr) 
Count of the distinct values of expr 
COV(expr1, expr2) 
Alias for COVAR_POP 
COVAR(expr1, expr2) 
Alias for COVAR_POP 
COVARIANCE(expr1, expr2) 
Alias for COVAR_POP 
COVAR_POP(expr1, expr2) 
Calculates the population covariance of expr1 and expr2 
COVAR_SAMP(expr1, expr2) 
Calculates the sample covariance of expr1 and expr2 
GROUPING(expr) 
Used primarily with Rollup, Cube, and Grouping Sets, to distinguish the source of
null values in an aggregated result set, returns whether For example, in a 
KURT(expr) 
Alias for KURTOSIS_POP 
KURTOSIS(expr) 
Alias for KURTOSIS_POP 
KURTOSIS_POP(expr) 
Calculate the population kurtosis of expr 
KURTOSIS_SAMP(expr) 
Calculate the sample kurtosis of expr 
KURT_POP(expr) 
Alias for KURTOSIS_POP 
KURT_SAMP(expr) 
Alias for KURTOSIS_SAMP 
MAX(expr) 
The maximum of values of expr 
MEAN(expr) 
Alias for AVG 
MIN(expr) 
The minimum of values of expr 
SKEW(expr) 
Alias for SKEWNESS_POP 
SKEWNESS(expr) 
Alias for SKEWNESS_POP 
SKEWNESS_POP(expr) 
Calculate the population skew of expr 
SKEWNESS_SAMP(expr) 
Calculate the sample skew of expr 
SKEW_POP(expr) 
Alias for SKEWNESS_POP 
SKEW_SAMP(expr) 
Alias for SKEWNESS_SAMP 
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 N1) 
SUM(expr) 
The sum of values of expr 
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 N1) 
Some examples of aggregate expressions:
SUM(sale_price)  SUM(base_price)
MAX(CEIL(x))  MIN(FLOOR(x))
AVG(ABS(z  100.0))