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, MILLISECOND, 
            MICROSECOND, NANOSECOND, MILLENNIUM, CENTURY, DECADE, WEEK, 
            QUARTERDAY]
EXTRACT    [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, 
            MICROSECOND, NANOSECOND, DOW, ISODOW, DOY, EPOCH, QUARTERDAY, 
            WEEK, DATEEPOCH]
DATEDIFF   [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, 
            MICROSECOND, NANOSECOND, WEEK]

Supported interval types:

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

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.

Statistical and Aggregate Functions

Both double-precision (standard) and single-precision floating point statistical functions are provided. Single-precision functions run faster on GPUs but might cause overflow errors.

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. When data cardinalities permit, OmniSci uses the precise implementation of COUNT(DISTINCT x) for APPROX_COUNT_DISTINCT.
  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.