Type casting comprises three principal actions: conversion, reinterpretation, and promotion.

**Conversion**. This refers to the process of changing the data type of a value to another data type. This involves additional computations and is exclusively performed by the`TO_STRING()`

function.**Reinterpretation**. This involves treating the binary data representing a value as if it were of a different data type, without actually changing the underlying data. This is handled by`SINT()`

, doesn't involve extra computations; instead, it merely reinterprets existing data.**Promotion**. This refers to the process of converting a value to a "larger" or more precise data type. It doesn't require extra computation either; it merely requests the argument to deliver a value of a different type. Only JSON fields and a few other functions can promote their values to integers. If an argument cannot yield a value of a different type, the promotion will fail. For instance, the`TIMEDIFF()`

function usually returns a string, but can also return a number. So,`BIGINT(TIMEDIFF(1,2))`

will execute successfully, compelling`TIMEDIFF()`

to supply an integer value. Conversely,`DATE_FORMAT()`

solely returns strings and can't yield a number, meaning that`BIGINT(DATE_FORMAT(...))`

will fail.

This function promotes an integer argument to a 64-bit type, leaving floating-point arguments untouched. It's designed to ensure the evaluation of specific expressions (such as `a*b`

) in 64-bit mode, even if all arguments are 32-bit.

The `DOUBLE()`

function promotes its argument to a floating-point type. This is designed to help enforce the evaluation of numeric JSON fields.

The `INTEGER()`

function promotes its argument to a 64-bit signed type. This is designed to enforce the evaluation of numeric JSON fields.

This function forcefully converts its argument to a string type.

The `UINT()`

function promotes its argument to a 32-bit unsigned integer type.

The `UINT64()`

function promotes its argument to a 64-bit unsigned integer type.

The `SINT()`

function forcefully reinterprets its 32-bit unsigned integer argument as signed and extends it to a 64-bit type (since the 32-bit type is unsigned). For instance, 1-2 ordinarily evaluates to 4294967295, but `SINT(1-2)`

evaluates to -1.

`ALL(cond FOR var IN json.array)`

applies to JSON arrays and returns 1 if the condition is true for all elements in the array and 0 otherwise. `cond`

is a general expression that can also use `var`

as the current value of an array element within itself.

- ALL() with json
- ALL() with json ex. 2

`select *, ALL(x>0 AND x<4 FOR x IN j.ar) from tbl`

```
+------+--------------+--------------------------------+
| id | j | all(x>0 and x<4 for x in j.ar) |
+------+--------------+--------------------------------+
| 1 | {"ar":[1,3]} | 1 |
| 2 | {"ar":[3,7]} | 0 |
+------+--------------+--------------------------------+
2 rows in set (0.00 sec)
```

`ALL(mva)`

is a special constructor for multi-value attributes. When used with comparison operators (including comparison with `IN()`

), it returns 1 if all values from the MVA attribute are found among the compared values.

- ALL() with MVA
- ALL() with MVA and IN()

`select * from tbl where all(m) >= 1`

```
+------+------+
| id | m |
+------+------+
| 1 | 1,3 |
| 2 | 3,7 |
+------+------+
2 rows in set (0.00 sec)
```

To compare an MVA attribute with an array, avoid using `<mva> NOT ALL()`

; use `ALL(<mva>) NOT IN()`

instead.

- ALL() with MVA and NOT IN()

`select * from tbl where all(m) not in (2, 4)`

```
+------+------+
| id | m |
+------+------+
| 1 | 1,3 |
| 2 | 3,7 |
+------+------+
2 rows in set (0.00 sec)
```

`ALL(string list)`

is a special operation for filtering string tags.

If all of the words enumerated as arguments of `ALL()`

are present in the attribute, the filter matches. The optional `NOT`

inverts the logic.

This filter internally uses doc-by-doc matching, so in the case of a full scan query, it might be slower than expected. It is intended for attributes that are not indexed, like calculated expressions or tags in PQ tables. If you need such filtering, consider the solution of putting the string attribute as a full-text field, and then use the full-text operator `match()`

, which will invoke a full-text search.

- ALL() with strings
- ALL() with strings and NOT

`select * from tbl where tags all('bug', 'release')`

```
+------+---------------------------+
| id | tags |
+------+---------------------------+
| 1 | bug priority_high release |
| 2 | bug priority_low release |
+------+---------------------------+
2 rows in set (0.00 sec)
```

`ANY(cond FOR var IN json.array)`

applies to JSON arrays and returns 1 if the condition is true for any element in the array and 0 otherwise. `cond`

is a general expression that can also use `var`

as the current value of an array element within itself.

- ANY() with json
- ANY() with json ex. 2

`select *, ANY(x>5 AND x<10 FOR x IN j.ar) from tbl`

```
+------+--------------+---------------------------------+
| id | j | any(x>5 and x<10 for x in j.ar) |
+------+--------------+---------------------------------+
| 1 | {"ar":[1,3]} | 0 |
| 2 | {"ar":[3,7]} | 1 |
+------+--------------+---------------------------------+
2 rows in set (0.00 sec)
```

`ANY(mva)`

is a special constructor for multi-value attributes. When used with comparison operators (including comparison with `IN()`

), it returns 1 if any of the MVA values is found among the compared values.

When comparing an array using `IN()`

, `ANY()`

is assumed by default if not otherwise specified, but a warning will be issued regarding the missing constructor.

- ANY() with MVA
- ANY() with MVA and IN()

```
mysql> select * from tbl
+------+------+
| id | m |
+------+------+
| 1 | 1,3 |
| 2 | 3,7 |
+------+------+
2 rows in set (0.01 sec)
mysql> select * from tbl where any(m) > 5
+------+------+
| id | m |
+------+------+
| 2 | 3,7 |
+------+------+
1 row in set (0.00 sec)
```

```
+------+------+
| id | m |
+------+------+
| 1 | 1,3 |
| 2 | 3,7 |
+------+------+
2 rows in set (0.00 sec)
```

To compare an MVA attribute with an array, avoid using `<mva> NOT ANY()`

; use `<mva> NOT IN()`

instead or `ANY(<mva>) NOT IN()`

.

- ANY() with MVA and NOT IN()

```
mysql> select * from tbl
+------+------+
| id | m |
+------+------+
| 1 | 1,3 |
| 2 | 3,7 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from tbl where any(m) not in (1, 3, 5)
+------+------+
| id | m |
+------+------+
| 2 | 3,7 |
+------+------+
1 row in set (0.00 sec)
```

`ANY(string list)`

is a special operation for filtering string tags.

If any of the words enumerated as arguments of `ANY()`

is present in the attribute, the filter matches. The optional `NOT`

inverts the logic.

This filter internally uses doc-by-doc matching, so in the case of a full scan query, it might be slower than expected. It is intended for attributes that are not indexed, like calculated expressions or tags in PQ tables. If you need such filtering, consider the solution of putting the string attribute as a full-text field, and then use the full-text operator `match()`

, which will invoke a full-text search.

- ANY() with strings
- ANY() with strings and NOT

`select * from tbl where tags any('bug', 'feature')`

```
+------+---------------------------+
| id | tags |
+------+---------------------------+
| 1 | bug priority_high release |
| 2 | bug priority_low release |
+------+---------------------------+
2 rows in set (0.00 sec)
```

`CONTAINS(polygon, x, y)`

checks whether the (x,y) point is within the given polygon, and returns 1 if true, or 0 if false. The polygon has to be specified using either the POLY2D() function. The former function is intended for "small" polygons, meaning less than 500 km (300 miles) a side, and it doesn't take into account the Earth's curvature for speed. For larger distances, you should use `GEOPOLY2D`

, which tessellates the given polygon in smaller parts, accounting for the Earth's curvature.

The behavior of `IF()`

is slightly different from its MySQL counterpart. It takes 3 arguments, checks whether the 1st argument is equal to 0.0, returns the 2nd argument if it is not zero, or the 3rd one when it is. Note that unlike comparison operators, `IF()`

does **not** use a threshold! Therefore, it's safe to use comparison results as its 1st argument, but arithmetic operators might produce unexpected results. For instance, the following two calls will produce *different* results even though they are logically equivalent:

- IF()

```
IF ( sqrt(3)*sqrt(3)-3<>0, a, b )
IF ( sqrt(3)*sqrt(3)-3, a, b )
```

In the first case, the comparison operator <> will return 0.0 (false) due to a threshold, and `IF()`

will always return `**`

as a result. In the second case, the same `sqrt(3)*sqrt(3)-3`

expression will be compared with zero *without* a threshold by the `IF()`

function itself. However, its value will be slightly different from zero due to limited floating-point calculation precision. Because of this, the comparison with 0.0 done by `IF()`

will not pass, and the second variant will return 'a' as a result.

`HISTOGRAM(expr, {hist_interval=size, hist_offset=value})`

takes a bucket size and returns the bucket number for the value. The key function is:

`key_of_the_bucket = interval + offset * floor ( ( value - offset ) / interval )`

The histogram argument `interval`

must be positive. The histogram argument `offset`

must be positive and less than `interval`

. It is used in aggregation, `FACET`

, and grouping.

- HISTOGRAM()

```
SELECT COUNT(*),
HISTOGRAM(price, {hist_interval=100}) as price_range
FROM facets
GROUP BY price_range ORDER BY price_range ASC;
```

`IN(expr,val1,val2,...)`

takes 2 or more arguments and returns 1 if the 1st argument (expr) is equal to any of the other arguments (val1..valN), or 0 otherwise. Currently, all the checked values (but not the expression itself) are required to be constant. The constants are pre-sorted, and binary search is used, so `IN()`

even against a large arbitrary list of constants will be very quick. The first argument can also be an MVA attribute. In that case, `IN()`

will return 1 if any of the MVA values are equal to any of the other arguments. `IN()`

also supports `IN(expr,@uservar)`

syntax to check whether the value belongs to the list in the given global user variable. The first argument can be a JSON attribute.

`INDEXOF(cond FOR var IN json.array)`

function iterates through all elements in the array and returns the index of the first element for which 'cond' is true, and -1 if 'cond' is false for every element in the array.

`INTERVAL(expr,point1,point2,point3,...)`

takes 2 or more arguments and returns the index of the argument that is less than the first argument: it returns 0 if `expr<point1`

, 1 if `point1<=expr<point2`

, and so on. It is required that `point1<point2<...<pointN`

for this function to work correctly.

`LENGTH(attr_mva)`

function returns the number of elements in an MVA set. It works with both 32-bit and 64-bit MVA attributes. `LENGTH(attr_json)`

returns the length of a field in JSON. The return value depends on the type of field. For example, `LENGTH(json_attr.some_int)`

always returns 1, and `LENGTH(json_attr.some_array)`

returns the number of elements in the array. `LENGTH(string_expr)`

function returns the length of the string resulting from an expression.
TO_STRING() must enclose the expression, regardless of whether the expression returns a non-string or it's simply a string attribute.

`RANGE(expr, {range_from=value,range_to=value})`

takes a set of ranges and returns the bucket number for the value.
This expression includes the `range_from`

value and excludes the `range_to`

value for each range. A range can be open - having only the `range_from`

or only the `range_to`

value. It is used in aggregation, `FACET`

, and grouping.

- RANGE()

```
SELECT COUNT(*),
RANGE(price, {range_to=150},{range_from=150,range_to=300},{range_from=300}) price_range
FROM facets
GROUP BY price_range ORDER BY price_range ASC;
```

`REMAP(condition, expression, (cond1, cond2, ...), (expr1, expr2, ...))`

function allows you to make some exceptions to expression values depending on condition values. The condition expression should always result in an integer, while the expression can result in an integer or float.

- REMAP()
- Another example

```
SELECT id, size, REMAP(size, 15, (5,6,7,8), (1,1,2,2)) s
FROM products
ORDER BY s ASC;
```

This will put documents with sizes 5 and 6 first, followed by sizes 7 and 8. In case there's an original value not listed in the array (e.g. size 10), it will default to 15, and in this case, will be placed at the end.

Note, that `CURTIME()`

, `UTC_TIME()`

, `UTC_TIMESTAMP()`

, and `TIMEDIFF()`

can be promoted to numeric types using arbitrary conversion functions such as `BIGINT()`

, `DOUBLE()`

, etc.

Returns the current timestamp as an INTEGER.

- SQL

`select NOW();`

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

Returns the current time in the local timezone in `hh:ii:ss`

format.

- SQL

`select CURTIME();`

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

Returns the current date in the local timezone in `YYYY-MM-DD`

format.

- SQL

`select curdate();`

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

Returns the current time in UTC timezone in `hh:ii:ss`

format.

- SQL

`select UTC_TIME();`

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

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

format.

- SQL

`select UTC_TIMESTAMP();`

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

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

- SQL

`select second(now());`

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

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

- SQL

`select minute(now());`

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

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

- SQL

`select hour(now());`

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

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

- SQL

`select day(now());`

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

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

- SQL

`select month(now());`

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

Returns the integer quarter of the year (in 1..4 range) from a timestamp argument, according to the current timezone.

- SQL

`select quarter(now());`

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

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

- SQL

`select year(now());`

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

Returns the weekday name for a given timestamp argument, according to the current timezone.

- SQL

`select dayname(now());`

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

Returns the name of the month for a given timestamp argument, according to the current timezone.

- SQL

`select monthname(now());`

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

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.

- SQL

`select dayofweek(now());`

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

Returns the integer day of the year (in 1..366 range) for a given timestamp argument, according to the current timezone.

- SQL

`select dayofyear(now());`

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

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.

- SQL

`select yearweek(now());`

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

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

- SQL

`select yearmonth(now());`

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

Returns the integer year, month, and date code (ranging from 19691231 to 20380119) based on the current timezone.

- SQL

`select yearmonthday(now());`

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

Calculates the difference between two timestamps in the format `hh:ii:ss`

.

- SQL

`select timediff(1615787586, 1613787583);`

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

Calculates the number of days between two given timestamps.

- SQL

`select datediff(1615787586, 1613787583);`

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

Formats the date part from a timestamp argument as a string in `YYYY-MM-DD`

format.

- SQL

`select date(now());`

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

Formats the time part from a timestamp argument as a string in `HH:MM:SS`

format.

- SQL

`select time(now());`

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

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 documentation for `strftime()`

for your operating system to get the full list.

- SQL

`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 are specified using the unit name, such as `week`

or as a single unit like `1M`

. Multiple units such as `2w`

are not supported.

The valid intervals 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)

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.