• Downloads
  • Services
  • Resources

    • Documentation
    • Cheatsheets
    • Courses
    • Free Config Review
    • Blog
    • Forum
  • About

    • Manticore Search
    • Clients
  • Contact
POLY2D()
Functions > Geo spatial functions > POLY2D()
POLY2D(x1,y1,x2,y2,x3,y3...) creates a polygon to be used with the CONTAINS() function. This polygon assumes a flat Earth, so it should not be too large; for large areas, the GEOPOLY2D() function, which takes Earth's curvature into consideration, should be used.
DATEDIFF()
Functions > Date and time functions > DATEDIFF()
Calculates the number of days between two given timestamps.
QUARTER()
Functions > Date and time functions > QUARTER()
Returns the integer quarter of the year (in 1..4 range) from a timestamp argument, according to the current timezone.
MONTH()
Functions > Date and time functions > MONTH()
Returns the integer month (in 1..12 range) from a timestamp argument, according to the current timezone.
YEAR()
Functions > Date and time functions > YEAR()
Returns the integer year (in 1969..2038 range) from a timestamp argument, according to the current timezone.
MONTHNAME()
Functions > Date and time functions > MONTHNAME()
Returns the name of the month for a given timestamp argument, according to the current timezone.
DAYNAME()
Functions > Date and time functions > DAYNAME()
Returns the weekday name for a given timestamp argument, according to the current timezone.
DAYOFWEEK()
Functions > Date and time functions > DAYOFWEEK()
Returns the integer weekday index (in 1..7 range) for a given timestamp argument, according to the current timezone. Note that the week starts on Sunday.
YEARWEEK()
Functions > Date and time functions > YEARWEEK()
Returns the integer year and the day code of the first day of current week (in 1969001..2038366 range) for a given timestamp argument, according to the current timezone.
DAYOFYEAR()
Functions > Date and time functions > DAYOFYEAR()
Returns the integer day of the year (in 1..366 range) for a given timestamp argument, according to the current timezone.
YEARMONTHDAY()
Functions > Date and time functions > YEARMONTHDAY()
Returns the integer year, month, and date code (ranging from 19691231 to 20380119) based on the current timezone.
YEARMONTH()
Functions > Date and time functions > YEARMONTH()
Returns the integer year and month code (in 196912..203801 range) from a timestamp argument, according to the current timezone.
TIMEDIFF()
Functions > Date and time functions > TIMEDIFF()
Calculates the difference between two timestamps in the format hh:ii:ss .
DATE()
Functions > Date and time functions > DATE()
Formats the date part from a timestamp argument as a string in YYYY-MM-DD format.
DATE_FORMAT()
Functions > Date and time functions > DATE_FORMAT()
Returns a formatted string based on the provided date and format arguments. The format argument uses the same specifiers as the strftime function. For convenience, here are some common format specifiers: %Y - Four-digit year %m - Two-digit month (01-12) %d - Two-digit day of the month (01-31) %H - Two-digit hour (00-23) %M - Two-digit minute (00-59) %S - Two-digit second (00-59) %T - Time in 24-hour format ( %H:%M:%S ) Note that this is not a complete list of the specifiers. Please consult the ...
GEODIST()
Functions > Geo spatial functions > GEODIST()
GEODIST(lat1, lon1, lat2, lon2, [...]) function calculates the geosphere distance between two points specified by their coordinates. Note that by default, both latitudes and longitudes must be in radians, and the result will be in meters. You can use arbitrary expressions for any of the four coordinates. An optimized path will be chosen when one pair of arguments directly refers to a pair of attributes, and the other one is constant. GEODIST() also accepts an optional 5th argument, allowing you ...
TIME()
Functions > Date and time functions > TIME()
Formats the time part from a timestamp argument as a string in HH:MM:SS format.
DATE_HISTOGRAM()
Functions > Date and time functions > DATE_HISTOGRAM()
DATE_HISTOGRAM(expr, {calendar_interval='unit_name'}) or DATE_HISTOGRAM(expr, {fixed_interval='interval'}) takes a bucket size as a unit name or fixed interval and returns the bucket number for the value. Values are rounded to the closest bucket. The key function is: key_of_the_bucket = interval * floor ( value / interval ) Intervals can be specified using a unit name, like week , or as a single unit, such as 1M . However, multiple units, like 2d , are not supported with calendar_interval but ...
Date math
Functions > Date and time functions > DATE_RANGE() > Date math
Date math lets you work with dates and times directly in your searches. It's especially useful for handling data that changes over time. With date math, you can easily do things like find entries from a certain period, analyze data trends, or manage when information should be removed. It simplifies working with dates by letting you add or subtract time from a given date, round dates to the nearest time unit, and more, all within your search queries. To use date math, you start with a base date, ...
DATE_RANGE()
Functions > Date and time functions > DATE_RANGE()
DATE_RANGE(expr, {range_from='date_math', range_to='date_math'}) takes a set of ranges and returns the bucket number for the value. The expression includes the range_from value and excludes the range_to value for each range. The range can be open - having only the range_from or only the range_to value. The difference between this and the RANGE() function is that the range_from and range_to values can be expressed in Date math expressions. Used in aggregation, FACET , and grouping. Example: ...
String functions
Functions > String functions
String functions CONCAT() Concatenates two or more strings into one. Non-string arguments must be explicitly converted to string using the TO_STRING() function. CONCAT(TO_STRING(float_attr), ',', TO_STRING(int_attr), ',', title)
LEVENSHTEIN()
Functions > String functions > LEVENSHTEIN()
LEVENSHTEIN ( pattern, source, {normalize=0, length_delta=0}) returns number (Levenshtein distance) of single-character edits (insertions, deletions or substitutions) between pattern and source strings required to make in pattern to make it source . pattern , source - constant string, string field name, JSON field name, or any expression that produces a string (like e.g., SUBSTRING_INDEX()) normalize - option to return the distance as a float number in the range [0.0 - 1.0] , where 0.0 is an ...
CONCAT()
Functions > String functions > CONCAT()
Concatenates two or more strings into one. Non-string arguments must be explicitly converted to string using the TO_STRING() function. CONCAT(TO_STRING(float_attr), ',', TO_STRING(int_attr), ',', title)
SNIPPET()
Functions > String functions > SNIPPET()
The SNIPPET() function can be used to highlight search results within a given text. The first two arguments are: the text to be highlighted, and a query. Options can be passed to the function as the third, fourth, and so on arguments. SNIPPET() can obtain the text for highlighting directly from the table. In this case, the first argument should be the field name: SELECT SNIPPET(body, QUERY()) FROM myIndex WHERE MATCH('my.query') In this example, the QUERY() expression returns the current full- ...
REGEX()
Functions > String functions > REGEX()
The REGEX(attr,expr) function returns 1 if a regular expression matches the attribute's string, and 0 otherwise. It works with both string and JSON attributes. SELECT REGEX(content, 'box?') FROM test; SELECT REGEX(j.color, 'red | pink') FROM test; Expressions should adhere to the RE2 syntax. To perform a case-insensitive search, for instance, you can use: SELECT REGEX(content, '(?i)box') FROM test;
SUBSTRING_INDEX()
Functions > String functions > SUBSTRING_INDEX()
SUBSTRING_INDEX(string, delimiter, number) returns a substring of the original string, based on a specified number of delimiter occurrences: string - The original string, which can be a constant string or a string from a string/JSON attribute. delimiter - The delimiter to search for. number - The number of times to search for the delimiter. This can be either a positive or negative number. If it is a positive number, the function will return everything to the left of the delimiter. If it is a ...
Geo spatial functions
Functions > Geo spatial functions
Geo spatial functions GEODIST() GEODIST(lat1, lon1, lat2, lon2, [...]) function calculates the geosphere distance between two points specified by their coordinates. Note that by default, both latitudes and longitudes must be in radians, and the result will be in meters. You can use arbitrary expressions for any of the four coordinates. An optimized path will be chosen when one pair of arguments directly refers to a pair of attributes, and the other one is constant. GEODIST() also accepts an ...
UPPER() and LOWER()
Functions > String functions > UPPER() and LOWER()
UPPER(string) convert argument to upper case, LOWER(string) convert argument to lower case. Result also can be promoted to numeric, but only if string argument is convertible to a number. Numeric values could be promoted with arbitrary functions ( BITINT , DOUBLE , etc.). SELECT upper('www.w3schools.com', '.', 2); /* WWW.W3SCHOOLS.COM */ SELECT double (upper ('1.2e3')); /* 1200.000000 */ SELECT integer (lower ('12345')); /* 12345 */
GEOPOLY2D()
Functions > Geo spatial functions > GEOPOLY2D()
GEOPOLY2D(lat1,lon1,lat2,lon2,lat3,lon3...) creates a polygon to be used with the CONTAINS() function. This function takes into account the Earth's curvature by tessellating the polygon into smaller ones, and should be used for larger areas. For small areas, the POLY2D() function can be used instead. The function expects coordinates to be pairs of latitude/longitude coordinates in degrees; if radians are used, it will give the same result as POLY2D() .
HOUR()
Functions > Date and time functions > HOUR()
Returns the integer hour (in 0..23 range) from a timestamp argument, according to the current timezone.