Functions and Operators

Basic Mathematical Operators

Mathematical Operator Precedence

1. Parenthesization
2. Multiplication and division
3. Addition and subtraction

Comparison Operators

Mathematical Functions

Trigonometric Functions

Geometric Functions

String Functions

Pattern-matching Functions

Usage Notes

The following wildcard characters are supported by LIKE and ILIKE:

• % matches any number of characters, including zero characters.
• _ matches exactly one character.

Date/Time Functions

SELECT DATE_TRUNC(MINUTE, arr_timestamp) Arrival \
FROM flights_2008_10k LIMIT 10;

SELECT EXTRACT(HOUR FROM arr_timestamp) Arrival_Hour \
FROM flights_2008_10k LIMIT 10;

SELECT arr_timestamp + INTERVAL '10' YEAR \
FROM flights_2008_10k LIMIT 10;

SELECT NOW() FROM flights_2008_10k LIMIT 1;

SELECT TIMESTAMPADD(DAY, 14, arr_timestamp) Fortnight \
FROM flights_2008_10k LIMIT 10;

SELECT TIMESTAMPDIFF(MINUTE, arr_timestamp, dep_timestamp) Flight_Time \
FROM flights_2008_10k LIMIT 10;

SELECT DATEDIFF('YEAR', plane_issue_date, now()) Years_In_Service \
FROM flights_2008_10k LIMIT 10;

SELECT DATEADD('MINUTE', 6000, dep_timestamp) Arrival_Estimate \
FROM flights_2008_10k LIMIT 10;

SELECT DATEPART('YEAR', plane_issue_date) Year_Issued \
FROM flights_2008_10k LIMIT 10;

Usage Notes

Supported date_part types:

DATE_TRUNC [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLENNIUM, CENTURY, DECADE, WEEK, 
            QUARTERDAY]
EXTRACT    [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, ISODOW, DOY, EPOCH, QUARTERDAY, 
            WEEK]
DATEDIFF   [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLENNIUM, CENTURY, DECADE, 
            QUARTERDAY]

Supported interval types:

DATEADD  [DECADE, YEAR, QUARTER, MONTH, WEEK, WEEKDAY, DAY, 
               HOUR, MINUTE, SECOND ]
TIMESTAMPADD  [YEAR, QUARTER, MONTH, WEEKDAY, DAY, HOUR, MINUTE,
               SECOND ]
DATEPART      [YEAR, QUARTER, MONTH, DAYOFYEAR, QUARTERDAY, WEEKDAY, DAY, HOUR,
               MINUTE, SECOND ]

Accepted Date, Time, and Timestamp Formats

Usage Notes

• For two-digit years, years 69-99 are assumed to be previous century (for example, 1969), and 0-68 are assumed to be current century (for example, 2016).
• For four-digit years, negative years (BC) are not supported.
• Hours are expressed in 24-hour format.
• When time components are separated by colons, you can write them as one or two digits.
• Months are case insensitive. You can spell them out or abbreviate to three characters.
• For timestamps, decimal seconds are ignored. Time zone offsets are written as +/-HHMM.
• For timestamps, a numeric string is converted to +/- seconds since January 1, 1970. Supported timestamps range from -30610224000 (January 1, 1000) through 29379456000 (December 31, 2900).
• On output, dates are formatted as YYYY-MM-DD. Times are formatted as HH:MM:SS.
• Linux EPOCH values range from -30610224000 (1/1/1000) through 185542587100800 (1/1/5885487). Complete range in years: +/-5,883,517 around epoch.

Aggregate Functions

SELECT city, SAMPLE(state), sum (population)
FROM census_table
GROUP BY city

Usage Notes

• COUNT(DISTINCT x), especially when used in conjunction with GROUP BY, can require a very large amount of memory to keep track of all distinct values in large tables with large cardinalities. To avoid this large overhead, use APPROX_COUNT_DISTINCT.

• APPROX_COUNT_DISTINCT(x, e) gives an approximate count of the value x, based on an expected error rate defined in e. The error rate is an integer value from 1 to 100. The lower the value of e, the higher the precision, and the higher the memory cost. Select a value for e based on the level of precision required. On large tables with large cardinalities, consider using APPROX_COUNT_DISTINCT when possible to preserve memory.

  Set the default error rate using the -hll-precision-bits configuration paramenter.

• Currently, OmniSci does not support grouping by non-dictionary-encoded strings. However, with the SAMPLE aggregate function, you can select non-dictionary-encoded strings that are presumed to be unique in a group. For example:

  SELECT user_name, SAMPLE(user_decription) FROM tweets GROUP BY user_name;
  

  If the aggregated column (user_description in the example above) is not unique within a group, SAMPLE selects a value that might be nondeterministic because of the parallel nature of OmniSci query execution.