Note, that CURTIME(), UTC_TIME(), UTC_TIMESTAMP(), and TIMEDIFF() can be promoted to numeric types using arbitrary conversion functions such as BIGINT(), DOUBLE(), etc.

select NOW();

+------------+
| NOW()      |
+------------+
| 1615788407 |
+------------+

select CURTIME();

+-----------+
| CURTIME() |
+-----------+
| 07:06:30  |
+-----------+

select curdate();

+------------+
| curdate()  |
+------------+
| 2023-08-02 |
+------------+

select UTC_TIME();

+------------+
| UTC_TIME() |
+------------+
| 06:06:18   |
+------------+

select UTC_TIMESTAMP();

+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2021-03-15 06:06:03 |
+---------------------+

select second(now());

+---------------+
| second(now()) |
+---------------+
| 52            |
+---------------+

select minute(now());

+---------------+
| minute(now()) |
+---------------+
| 5             |
+---------------+

select hour(now());

+-------------+
| hour(now()) |
+-------------+
| 7           |
+-------------+

select day(now());

+------------+
| day(now()) |
+------------+
| 15         |
+------------+

select month(now());

+--------------+
| month(now()) |
+--------------+
| 3            |
+--------------+

select quarter(now());

+----------------+
| quarter(now()) |
+----------------+
| 2              |
+----------------+

select year(now());

+-------------+
| year(now()) |
+-------------+
| 2024        |
+-------------+

select dayname(now());

+----------------+
| dayname(now()) |
+----------------+
| Wednesday      |
+----------------+

select monthname(now());

+------------------+
| monthname(now()) |
+------------------+
| August           |
+------------------+

select dayofweek(now());

+------------------+
| dayofweek(now()) |
+------------------+
| 5                |
+------------------+

select dayofyear(now());

+------------------+
| dayofyear(now()) |
+------------------+
|              214 |
+------------------+

select yearweek(now());

+-----------------+
| yearweek(now()) |
+-----------------+
|         2023211 |
+-----------------+

select yearmonth(now());

+------------------+
| yearmonth(now()) |
+------------------+
| 202103           |
+------------------+

select yearmonthday(now());

+---------------------+
| yearmonthday(now()) |
+---------------------+
| 20210315            |
+---------------------+

select timediff(1615787586, 1613787583);

+----------------------------------+
| timediff(1615787586, 1613787583) |
+----------------------------------+
| 555:33:23                        |
+----------------------------------+

select datediff(1615787586, 1613787583);

+----------------------------------+
| datediff(1615787586, 1613787583) |
+----------------------------------+
|                               23 |
+----------------------------------+

select date(now());

+-------------+
| date(now()) |
+-------------+
| 2023-08-02  |
+-------------+

select time(now());

+-------------+
| time(now()) |
+-------------+
| 15:21:27    |
+-------------+

SELECT DATE_FORMAT(NOW(), 'year %Y and time %T');

+------------------------------------------+
| DATE_FORMAT(NOW(), 'year %Y and time %T') |
+------------------------------------------+
| year 2023 and time 11:54:52              |
+------------------------------------------+

This example formats the current date and time, displaying the four-digit year and the time in 24-hour format.

DATE_HISTOGRAM(expr, {calendar_interval='unit_name'}) Takes a bucket size as a unit name 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 are allowed with fixed_interval.

The valid intervals for calendar_interval are:

• minute, 1m
• hour, 1h
• day, 1d
• week, 1w (a week is the interval between the start day of the week, hour, minute, second and the next week but the same day and time of the week)
• month, 1M
• year, 1y (a year is the interval between the start day of the month, time and the next year but the same day of the month, time)

The valid intervals for fixed_interval are:

• minute, 2m
• hour, 3h
• day, 5d

Used in aggregation, FACET, and grouping.

Example:

SELECT COUNT(*),
DATE_HISTOGRAM(tm, {calendar_interval='month'}) AS months
FROM facets
GROUP BY months ORDER BY months ASC;

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:

SELECT COUNT(*),
DATE_RANGE(tm, {range_to='2017||+2M/M'},{range_from='2017||+2M/M',range_to='2017||+5M/M'},{range_from='2017||+5M/M'}) AS points
FROM idx_dates
GROUP BY points ORDER BY points ASC;

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, which can be:

• now for the current date and time,
• or a specific date string ending with ||.

Then, you can modify this date with operations like:

• +1y to add one year,
• -1h to subtract one hour,
• /m to round to the nearest month.

You can use these units in your operations:

• s for seconds,
• m for minutes,
• h (or H) for hours,
• d for days,
• w for weeks,
• M for months,
• y for years.

Here are some examples of how you might use date math:

• now+4h means four hours from now.
• now-2d/d is the time two days ago, rounded to the nearest day.
• 2010-04-20||+2M/d is June 20, 2010, rounded to the nearest day.

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 to easily convert between input and output units and select the specific geodistance formula to use. The complete syntax and a few examples are as follows:

GEODIST(lat1, lon1, lat2, lon2, { option=value, ... })

GEODIST(40.7643929, -73.9997683, 40.7642578, -73.9994565, {in=degrees, out=feet})

GEODIST(51.50, -0.12, 29.98, 31.13, {in=deg, out=mi})

The known options and their values are:

• in = {deg | degrees | rad | radians}, specifies the input units;
• out = {m | meters | km | kilometers | ft | feet | mi | miles}, specifies the output units;
• method = {adaptive | haversine}, specifies the geodistance calculation method.

The default method is "adaptive". It is a well-optimized implementation that is both more precise and much faster at all times than "haversine".

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().

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.

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 ( 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 exact match, and 1.0 is the maximum difference. The default value is 0, meaning not to normalize and provide the result as an integer.
• length_delta - skips Levenshtein distance calculation and returns max(strlen(pattern), strlen(source)) if the option is set and the lengths of the strings differ by more than the length_delta value. The default value is 0, meaning to calculate Levenshtein distance for any input strings. This option can be useful when checking mostly similar strings.

SELECT LEVENSHTEIN('gily', attr1) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC, dist ASC;
SELECT LEVENSHTEIN('gily', j.name, {length_delta=6}) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC;
SELECT LEVENSHTEIN(title, j.name, {normalize=1}) AS dist, WEIGHT() AS w FROM test WHERE MATCH ('test') ORDER BY w DESC, dist ASC;

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;

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-text query. SNIPPET() can also highlight non-indexed text:

mysql  SELECT id, SNIPPET('text to highlight', 'my.query', 'limit=100') FROM myIndex WHERE MATCH('my.query')

Additionally, it can be used to highlight text fetched from other sources using a User-Defined Function (UDF):

SELECT id, SNIPPET(myUdf(id), 'my.query', 'limit=100') FROM myIndex WHERE MATCH('my.query')

In this context, myUdf() is a User-Defined Function (UDF) that retrieves a document by its ID from an external storage source. The SNIPPET() function is considered a "post limit" function, which means that the computation of snippets is delayed until the entire final result set is prepared, and even after the LIMIT clause has been applied. For instance, if a LIMIT 20,10 clause is used, SNIPPET() will be called no more than 10 times.

It is important to note that SNIPPET() does not support field-based limitations. For this functionality, use HIGHLIGHT() instead.

SELECT SUBSTRING_INDEX('www.w3schools.com', '.', 2) FROM test;
SELECT SUBSTRING_INDEX(j.coord, ' ', 1) FROM test;
SELECT          SUBSTRING_INDEX('1.2 3.4', ' ',  1);  /* '1.2' */
SELECT          SUBSTRING_INDEX('1.2 3.4', ' ', -1);  /* '3.4' */
SELECT sint (   SUBSTRING_INDEX('1.2 3.4', ' ',  1)); /* 1 */
SELECT sint (   SUBSTRING_INDEX('1.2 3.4', ' ', -1)); /* 3 */
SELECT double ( SUBSTRING_INDEX('1.2 3.4', ' ',  1)); /* 1.200000 */
SELECT double ( SUBSTRING_INDEX('1.2 3.4', ' ', -1)); /* 3.400000 */

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 */