Date and time functions

NOW()

Returns the current timestamp as an INTEGER.

  1. mysql> select NOW();
  2. +------------+
  3. | NOW()      |
  4. +------------+
  5. | 1615788407 |
  6. +------------+
  7. 1 row in set (0.00 sec)

CURTIME()

Returns the current time in local timezone in in hh:ii:ss format.

  1. mysql> select CURTIME();
  2. +-----------+
  3. | CURTIME() |
  4. +-----------+
  5. | 07:06:30  |
  6. +-----------+
  7. 1 row in set (0.00 sec)

UTC_TIME()

Returns the current time in UTC timezone in hh:ii:ss format.

  1. mysql> select UTC_TIME();
  2. +------------+
  3. | UTC_TIME() |
  4. +------------+
  5. | 06:06:18   |
  6. +------------+
  7. 1 row in set (0.00 sec)

UTC_TIMESTAMP()

Returns the current time in UTC timezone in YYYY-MM-DD hh:ii:ss format.

  1. mysql> select UTC_TIMESTAMP();
  2. +---------------------+
  3. | UTC_TIMESTAMP()     |
  4. +---------------------+
  5. | 2021-03-15 06:06:03 |
  6. +---------------------+
  7. 1 row in set (0.00 sec)

SECOND()

Returns the integer second (in 0..59 range) from a timestamp argument, according to the current timezone.

  1. mysql> select second(now());
  2. +---------------+
  3. | second(now()) |
  4. +---------------+
  5. | 52            |
  6. +---------------+
  7. 1 row in set (0.00 sec)

MINUTE()

Returns the integer minute (in 0..59 range) from a timestamp argument, according to the current timezone.

  1. mysql> select minute(now());
  2. +---------------+
  3. | minute(now()) |
  4. +---------------+
  5. | 5             |
  6. +---------------+
  7. 1 row in set (0.00 sec)

HOUR()

Returns the integer hour (in 0..23 range) from a timestamp argument, according to the current timezone.

  1. mysql> select hour(now());
  2. +-------------+
  3. | hour(now()) |
  4. +-------------+
  5. | 7           |
  6. +-------------+
  7. 1 row in set (0.00 sec)

DAY()

Returns the integer day of month (in 1..31 range) from a timestamp argument, according to the current timezone.

  1. mysql> select day(now());
  2. +------------+
  3. | day(now()) |
  4. +------------+
  5. | 15         |
  6. +------------+
  7. 1 row in set (0.00 sec)

MONTH()

Returns the integer month (in 1..12 range) from a timestamp argument, according to the current timezone.

  1. mysql> select month(now());
  2. +--------------+
  3. | month(now()) |
  4. +--------------+
  5. | 3            |
  6. +--------------+
  7. 1 row in set (0.00 sec)

YEAR()

Returns the integer year (in 1969..2038 range) from a timestamp argument, according to the current timezone.

  1. mysql> select year(now());
  2. +-------------+
  3. | year(now()) |
  4. +-------------+
  5. | 2021        |
  6. +-------------+
  7. 1 row in set (0.00 sec)

YEARMONTH()

Returns the integer year and month code (in 196912..203801 range) from a timestamp argument, according to the current timezone.

  1. mysql> select yearmonth(now());
  2. +------------------+
  3. | yearmonth(now()) |
  4. +------------------+
  5. | 202103           |
  6. +------------------+
  7. 1 row in set (0.00 sec)

YEARMONTHDAY()

Returns the integer year, month, and date code (in 19691231..20380119 range) from a timestamp argument, according to the current timezone.

  1. mysql> select yearmonthday(now());
  2. +---------------------+
  3. | yearmonthday(now()) |
  4. +---------------------+
  5. | 20210315            |
  6. +---------------------+
  7. 1 row in set (0.00 sec)

TIMEDIFF()

Returns difference between the timstamps in format hh:ii:ss. Example:

  1. mysql> select timediff(1615787586, 1613787583);
  2. +----------------------------------+
  3. | timediff(1615787586, 1613787583) |
  4. +----------------------------------+
  5. | 555:33:23                        |
  6. +----------------------------------+
  7. 1 row in set (0.00 sec)

Geo spatial functions

GEODIST()

GEODIST(lat1, lon1, lat2, lon2, \[...\]) function computes geosphere distance between two given 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 expression as any of the four coordinates. An optimized path will be selected when one pair of the arguments refers directly to a pair attributes and the other one is constant.

GEODIST() also takes an optional 5th argument that lets you easily convert between input and output units, and pick the specific geodistance formula to use. The complete syntax and a few examples are as follows:

  1. GEODIST(lat1, lon1, lat2, lon2, { option=value, ... })
  3. GEODIST(40.7643929, -73.9997683, 40.7642578, -73.9994565, {in=degrees, out=feet})
  5. 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 well optimized implementation that is both more precise and much faster at all times than “haversine”.

GEOPOLY2D()

GEOPOLY2D(lat1,lon1,lat2,lon2,lat3,lon3...) produces 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 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 same result as POLY2D().

POLY2D()

POLY2D(x1,y1,x2,y2,x3,y3...) produces 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 in consideration should be used.

String functions

CONCAT()

Concatenates two or more strings into one. Non-string arguments must be explicitly converted to string using TO_STRING() function

  1. CONCAT(TO_STRING(float_attr), ',', TO_STRING(int_attr), ',', title)

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 which produces a string (like e.g. SUBSTRING_INDEX()))
  • normalize - option to get distance as a float number in the range of [0.0 - 1.0], where 0.0 is the exact match and 1.0 is the maximum difference. Default value is 0, means to not normalize and provide the result as integer.
  • length_delta - to skip Levenshtein distance calculation and return max(strlen(pattern), strlen(source)) in case the option is set and lengths of the strings differ more than by length_delta value. Default value is 0, means to calculate Levenshtein distance for any input strings. This option could be useful in case of checking mostly similar strings.
  1. SELECT LEVENSHTEIN('gily', attr1) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC, dist ASC;
  2. SELECT LEVENSHTEIN('gily', j.name, {length_delta=6}) AS dist, WEIGHT() AS w FROM test WHERE MATCH('test') ORDER BY w DESC;
  3. SELECT LEVENSHTEIN(title, j.name, {normalize=1}) AS dist, WEIGHT() AS w FROM test WHERE MATCH ('test') ORDER BY w DESC, dist ASC;

REGEX()

REGEX(attr,expr) function returns 1 if regular expression matched to string of attribute and 0 otherwise. It works with both string and JSON attributes.

  1. SELECT REGEX(content, 'box?') FROM test;
  2. SELECT REGEX(j.color, 'red | pink') FROM test;

The expression should follow the RE2 syntax. For example, for case insensitive search you can do:

  1. SELECT REGEX(content, '(?i)box') FROM test;

SNIPPET()

SNIPPET() can be used to highlight search results in a given text. The first two arguments are: the text to highlight, and a query. It’s possible to pass options to the function as the third, fourth and so on arguments. SNIPPET() can fetch the text to use in highlighting from the table itself. The first argument in this case should be field name:

  1. SELECT SNIPPET(body, QUERY()) FROM myIndex WHERE MATCH('my.query')

QUERY() expression in this example returns current fulltext query. SNIPPET() can also highlight non-indexed text:

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

It can also be used to highlight the text fetched from other sources using an UDF:

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

where myUdf() would be a UDF that fetches a document by its ID from some external storage. SNIPPET() is a “post limit” function, meaning that computing snippets is postponed not just until the entire final result set is ready, but even after the LIMIT clause is applied. For example, with a LIMIT 20,10 clause, SNIPPET() will be called at most 10 times.

Note, SNIPPET() doesn’t support limiting by fields. Use HIGHLIGHT() instead.

SUBSTRING_INDEX()

SUBSTRING_INDEX(string, delimiter, number) returns a substring of a string before a specified number of delimiter occurs

  • string - The original string. 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. Can be both a positive or negative number.If it is a positive number, this function will return all to the left of the delimiter. If it is a negative number, this function will return all to the right of the delimiter.
  1. SELECT SUBSTRING_INDEX('www.w3schools.com', '.', 2) FROM test;
  2. SELECT SUBSTRING_INDEX(j.coord, ' ', 1) FROM test;