Mathematical Capabilities

Comparison Operator Support

Mathematical Function Support

Trigonometric Function Support

Geometric Function Support

String Function Support

Pattern-Matching Support

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 Function Support

Usage Notes

The 'date_part' argument must be enclosed in single quotes. For example, in the following query, the 'DAY' argument is in single quotes:

SELECT DATEDIFF('DAY', NOW(),plane_issue_date) from flights_2008_7M;

The interval argument does not require quotes, but the numeric value associated with the interval must be enclosed in single quotes. For example, if setting the interval to one day, the number 1 must be in single quotes:

SELECT INTERVAL '1' DAY FROM flights_2008_7M LIMIT 1;

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]
DATEDIFF   [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, 
            MICROSECOND, NANOSECOND, MILLENNIUM, CENTURY, DECADE, 
            QUARTERDAY]

Supported interval types:

DATEADD      [YEAR, QUARTER, MONTH, DAY, WEEKDAY, HOUR, MINUTE,
              SECOND, MILLISECOND, MICROSECOND, NANOSECOND]
TIMESTAMPADD [YEAR, QUARTER, MONTH, DAY, WEEKDAY, HOUR, MINUTE,
              SECOND, MILLISECOND, MICROSECOND, NANOSECOND]
DATEPART     [YEAR, QUARTER, MONTH, DAYOFYEAR, 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.
• On output, dates are formatted as YYYY-MM-DD. Times are formatted as HH:MM:SS.

Aggregate Function Support

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.