SQL Syntax
Updated at：2025-11-03
Directory
1.[Syntax support](#Syntax support)
2.Operators
3.[Built-in functions](#Built-in functions)
3.1[Type conversion functions](#Type conversion functions)
3.2[Aggregation functions](#Aggregation functions)
3.3[String functions](#String functions)
3.4[ Mathematical functions](#Mathematical functions)
3.5[ Estimation functions](#Estimation functions)
3.6[Date and time functions](#Date and time functions)
3.7[ Conditional functions](#Conditional functions)
3.8[Year-on-year and month-on-month functions](#Year-on-year and month-on-month functions)
4.Appendix
4.1 [Date format](#Date format)
4.2 Keywords
Syntax support
BLS supports basic SELECT queries. The specific query syntax is
SQL
1SELECT
2 select_expr [, select_expr] ...
3 [FROM subquery [AS] table_id]
4 [WHERE where_condition]
5 [GROUP BY {col_name | expr}, ... ]
6 [HAVING where_condition]
7 [ORDER BY {col_name | expr} [ASC | DESC], ...]
8 [LIMIT [offset,] row_count]In this context, where_condition is a conditional expression that evaluates to a Boolean value. When subqueries are unnecessary, the FROM clause can be omitted.
The field name is case-sensitive, and the use of keywords should be avoided whenever possible. If keywords are used, they must be enclosed in back quotes, for example: `action`.
Operator
Unary prefix operation
| Operator | Example | Description |
|---|---|---|
| +/- | -A | Symbol that changes the parameter |
Binary operation
| Operator | Example | Description |
|---|---|---|
| + | A + B | Addition operation |
| - | A - B | Subtraction operation |
| * | A * B | Multiplication operation |
| / | A / B | Division operation |
| % | A % B | Modulus operation, yielding the remainder of A divided by B |
| -> | column->path | Abbreviation of json_extract(), extracts content at the specified path from a specified column's JSON string, e.g., json->"$.b" |
Relational operation
| Operator | Example | Description |
|---|---|---|
| = | A = B | If A equals B, return TRUE; otherwise, return FALSE. If A and B are not of comparable types, return NULL. |
| != | A != B | If A is not equal to B, return TRUE; otherwise, return FALSE. If A and B are not of comparable types, return NULL. |
| > | A > B | If A is greater than B, return TRUE; otherwise, return FALSE. If A and B are not of comparable types, return NULL. |
| >= | A >= B | If A is greater than or equal to B, return TRUE; otherwise, return FALSE. If A and B are not of comparable types, return NULL. |
| < | A < B | If A is less than B, return TRUE; otherwise, return FALSE. If A and B are not of comparable types, return NULL. |
| <= | A <= B | If A is less than or equal to B, return TRUE; otherwise, return FALSE. If A and B are not comparable, return NULL. |
| [NOT] LIKE | A LIKE pattern | If A does [not] match the pattern, return TRUE; otherwise, return FALSE |
| IS [NOT] NULL | A IS NULL | If A is [not] NULL, return TRUE; otherwise, return FALSE |
| IS [NOT] TRUE/FALSE | A IS TRUE | If A is [not] TRUE/FALSE, return TRUE; otherwise, return FALSE |
| BETWEEN | EXPR BETWEEB A AND B | If the value of expression EXPR is greater than or equal to A and less than or equal to B, return TRUE; otherwise, return FALSE, equivalent to EXPRESSION >= A AND EXPRESSION <= B |
Logical operation
| Operator | Example | Description |
|---|---|---|
| [NOT] IN | A IN (val1, val2, ...) | If A is [not] equal to any of the parameter values, return TRUE; otherwise, return FALSE |
| AND | A AND B | If both A and B are TRUE, return TRUE; otherwise, return FALSE. If either A or B is not a Boolean, return NULL. |
| OR | A OR B | If either A or B is TRUE, return TRUE; otherwise, return FALSE. If either A or B is not a Boolean, return NULL. |
| NOT | NOT A | If A is FALSE, return TRUE; otherwise, return FALSE. If A is not a Boolean, return NULL. |
Built-in functions
Type conversion functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| cast(expr as <type>) | <type> | Convert the value of expr to the <type> type, and<type> supports BIGINT, DECIMAL, VARCHAR, TIMESTAMP | >select cast("123" as BIGINT) 123 |
Aggregation functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| count(*),count(expr),count(DISTINCT expr) | Int | Calculate the number of result rows meeting the condition | >select count(*) 10 |
| count_if(expr) | Int | Calculate the number of result rows that meet the expr expression as true | >select count_if(num > 0) 10 |
| sum(col) | T | Calculate the sum of elements | >select sum(num) 983 |
| avg(col) | Double | Calculate the average value of elements | >select avg(num) 73.14 |
| max(col) | T | Calculate the maximum value of elements | >select max(num) 99 |
| min(col) | T | Calculate the minimum value of elements | >select min(num) 62 |
| first(col) | T | Calculate the first value of elements | >select first(num) 87 |
| last(col) | T | Calculate the last value of elements | >select last(num) 95 |
| arbitrary(col) | T | Return the arbitrary value | >select any(num) 21 |
| bitwise_and_agg(col) | Int | Return the bitwise and operation (AND) result of all values | >select bitwise_and_agg(num) 1024 |
| bitwise_or_agg(col) | Int | Return the bitwise or operation (OR) result of all values | >select bitwise_and_agg(num) 2047 |
| bool_and(col) | Bool | Check if all expressions in the group meet the condition. If so, return true. | >select bool_and(result) true |
| bool_or(col) | Bool | Check if all expressions in the group meet the condition. If so, return true. | >select bool_and(result) false |
| checksum(col) | String | Calculate the checksum of elements within a group and output it in base64 encoding | >select checksum(x) dGhpcyBpcyBhIHRlc3Q= |
| max_by(x, y) | T | Query the x value corresponding to the maximum y | >select max_by(x,y) 32 |
| min_by(x,y) | T | Query the x value corresponding to the minimum y | >select min_by(x,y) 42 |
String functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| reverse(String str) | String | Return a string with reversed order | >select reverse("hello") olleh |
| lower(String str) | String | Return a string in lowercase format | >select lower("fOoBaR") foobar |
| upper(String str) | String | Return a string in uppercase format | >select upper("fOoBaR") FOOBAR |
| capitalize(String str) | String | Return a string with the first letter of each word capitalized | >select upper("fOoBaR") FOoBaR |
| substring(String str, Int start [, Int len]) | String | Extract a substring from the original string, starting from the given position and with the specified length. Start positions begin at 1 and support negative values, which count backward from the end of the string. If the length (len) parameter is omitted, the substring will extend to the end of the string. | >select substr("fOoBaR", 2, 4) OoBa >select substr("fOoBaR", -3, 2) Ba |
| substr(String str, Int start [, Int len]) | String | Alias for substring() | |
| replace(String str, String OLD, String NEW) | String | Return a string str in which OLD substring is replaced by NEW substring | >select replace("abcdef", "abc", "cba") cbadef |
| length(String str) | Int | Return the length of the string | >replace("abcdef", "abc", "cba") cbadef |
| chr(Int x) | String | Return the letter corresponding to the ASCII code | >chr(99) c |
| codepoint(char x) | Int | Return the ASCII code corresponding to the character | >codepoint('c') 99 |
| levenshtein_distance(String x, String y) | Int | Return the minimum edit distance between x and y | >levenshtein_distance('cg', 'cdefg') 3 |
| lpad(String x, Int length, String lpad_string) | String | Return a result string of specified length padded with specified characters at the beginning | >lpad('qqq',10,'p') pppppppqqq |
| rpad(String x, Int length, String lpad_string) | String | Return a result string of specified length padded with specified characters at the end | >rpad('qqq',10,'p') qqqppppppp |
| ltrim(String x) | String | Return the result string from which the space at the beginning of the string is deleted | >ltrim(' dhsk') dhsk |
| rtrim(String x) | String | Return the result string from which the space at the end of the string is deleted | >rtrim('dhsk ') dhsk |
| trim(String x) | String | Return the result string from which the space at the end of the string is deleted | >rtrim(' dhsk ') dhsk |
| normalize(String x) | String | Return a string formatted in NFC | >normalize('schön') schön |
| strpos(String x, String sub_string) | Int | Return the position of the target substring within the string | >strpos('china news','news') 7 |
| to_utf8(String x) | String | Return a string in UTF-8 encoding format | >to_utf8('info') aW5mbw== |
| locate(String substr, String str) | Int | Return the first occurrence position of substr in string str. If not found, return 0 | >select locate(".", "3.14") 2 |
| position(String substr, String str) | Int | Alias for locate() | |
| concat(String A, String B...) | String | Return a string concatenating all parameters in input order | >select concat("foo", "bar") foobar |
| json_extract(String json, String path) | T | Extract content from a JSON string based on the specified path | >select json_extract("{"a": 1, "b": 2}", "$.a") 1 |
| regexp_like(String str, String regexp) | Boolean | Whether a string matches the given regular expression | >select regexp_like("abc", "[a-z]+") true |
| regexp_extract(String str, String regexp) | String | Extract the first substring matching the regular expression from the string | >select regexp_extract("abc", "[a-z]+") abc |
| regexp_extract_all(String str, String regexp) | Array<String> | Extract all substrings matching the regular expression from the string | >select regexp_extract_all("abc22abc", "[a-z]+") [abc,abc] |
Mathematical functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| abs(Double a), abs(Int a) | Double/Int | Calculate the absolute value | >select abs(-2) 2 |
| sqrt(Double a) | Double | Calculate the square root | >select sqrt(100) 10 |
| greatest(T v1, T v2, ...) | T | Calculate the maximum value among the parameters. If any parameter is Null, return Null | >select greatest(1, 3.14, -5) 3.14 |
| least(T v1, T v2, ...) | T | Calculate the minimum value among the parameters. If any parameter is Null, return Null | >select least(1, 3.14, -5) -5 |
| rand() | Double | Return a random number between 0 and 1, with each row of the dataset receiving a different random number | >select rand() 0.3 |
| ceil(Double a) | Int | Return an integer greater than or equal to a and closest to a | >select ceil(3.14) 4 |
| floor(Double a) | Int | Return an integer less than or equal to a and closest to a | >select floor(3.14) 3 |
| log(Double a) | Double | Calculate base-2 logarithm | >select log(32) 5 |
| ln(Double a) | Double | Calculate natural logarithm | >select ln(100) 4.61512051684126 |
| pow(Double a， Double p) | Double | Calculate the p-th power of a | >select pow(2, 5) 32 |
| round(Key， n) | Double | Retain n decimal places for Key | >select round(200.3333, 2) 200.33 |
Estimation functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| percentile(Double x, Double percentage01, Double percentage02...) | Array<Double> | Sort x in ascending order and return x at positions percentage01, percentage02.. | >select percentile(latency, 0.1, 0.2) [0.22, 0.35] |
Date and time functions
Basic functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| now() | DateTime | Return the current local time | >select now() 2020-01-16T08:30:50Z |
| current_timestamp() | DateTime | Alias for now() | |
| unix_timestamp([String/DateTime date[, String format]]) | Int | Convert a datetime string or DateTime value into a Unix timestamp based on the specified format. By default, the ISO8601 format is supported and parsed according to the timezone in the string. If a specific format is provided, the local timezone will be used. | >select unix_timestamp("2019-11-11T11:11:11Z") 1573470671 >select unix_timestamp("2019-11-11 11:11:11", "%Y-%m-%d %H:%i:%s") 1573441871 |
| from_unixtime(Int unixtime[, String format]) | String | Convert unixtime (seconds from 1970-01-01 00:00:00 UTC to now) into a string representing local time, and the default format is 1970-01-01 00:00:00, with the option to specify string format via format. The supported date_format is detailed in Appendix | >select from_unixtime(0) 1970-01-01 08:00:00 >select from_unixtime(unix_timestamp("2019-11-11T11:11:11+08:00")) 2019-11-11 11:11:11 |
| str_to_date(String str, String format) | DateTime | Parse the datetime string str according to the format | >select str_to_date("2019-11-11 11:11:11", "%Y-%m-%d %H:%i:%s") 2019-11-11T03:11:11Z |
| year(String/DateTime date) | Int | Return the year of the date | >select year("2019-11-07T09:09:16+08:00") 2019 |
| quarter(String/DateTime date) | Int | Return the quarter of the date | >select quarter("2019-11-07T09:09:16+08:00") 4 |
| month(String/DateTime date) | Int | Return the month of the date | >select month("2019-11-07T09:09:16+08:00") 11 |
| day(String/DateTime date) | Int | Alias for dayofmonth() | |
| hour(String/DateTime date) | Int | Return the hour of the date | >select year("2019-11-07T09:09:16+08:00") 9 |
| minute(String/DateTime date) | Int | Return the minute of the date | >select year("2019-11-07T09:09:16+08:00") 9 |
| second(String/DateTime date) | Int | Return the second of the date | >select year("2019-11-07T09:09:16+08:00") 16 |
| weekday(String/DateTime date) | Int | Return the position of the date in a week (0 = Monday, 1 = Tuesday, ... 6 = Sunday) | >select weekday("2019-11-07T09:09:16+08:00") 3 |
| dayofyear(String/DateTime date) | Int | Return the position of date in the year, with possible values from 1 to 366 | >select dayofyear("2019-11-07T09:09:16+08:00") 311 |
| dayofmonth(String/DateTime date) | Int | Return the position of date within a month | >select dayofmonth("2019-11-07T09:09:16+08:00") 7 |
| dayofweek(String/DateTime date) | Int | Return the position of the date in a week (1 = Sunday, 2 = Monday, ... 7 = Saturday) | >select dayofweek("2019-11-07T09:09:16+08:00") 5 |
| current_timezone() | String | Return the current timezone | >select current_timezone() Asia/Shanghai |
| current_date | String | Return the current date | >select current_timezone() 2019-11-07 |
| extract(String unit, String/DateTime date) | String | Extract a specific value from the current date based on time unit , with unit options: "second/minute/hour/day/month/year" |
>select extract("day", "2019-11-07T09:09:16+08:00") 7 |
| date_trunc(String unit, String/DateTime date) | DateTime | Truncate the date by the specified time unit, such as "second/minute/hour/day/month/year" |
>select date_trunc("day", "2019-11-07T09:09:16+08:00") 2019-11-07T00:00:00 |
| date_diff(String unit, String/DateTime start, String/DateTime end) | Int | Calculate the difference between two time periods based on the time unit. Hour/minute/second/day are precise values; year and month are derived differences , with unit options: "second/minute/hour/day/month/year" |
>select date_diff("day","2019-11-05T09:09:16+08:00","2019-11-07T09:09:16+08:00") 2 |
| date_add(String unit, Int N, DateTime time) | TimeStamp | The time unit obtained by N time units after obtaining the time can be selected as "second/minute/hour/day/month/year" |
>select date_add('day', 2 ,cast('2025-08-15T19:28:42Z+08:00' as timestamp)) 2025-08-17 19:28:42.000 |
| localtime() | String | Get the current time (HH:MM:SS) | >select localtime() 10:09:33 |
| date(String/DateTime time) | String | Get the corresponding date (yy-mm-dd) | >select date("2019-11-05T09:09:16+08:00") 2019-11-05 |
| from_iso8601_timestamp(String time) | String | Convert an ISO8601-formatted date and time expression into a timestamp-type expression that includes a timezone | >select from_iso8601_timestamp("2019-11-05T09:09:16+08:00") 2019-11-05 09:09:16 Asia/Shanghai |
| from_iso8601_date(String time) | String | Convert an ISO8601-formatted date expression into a string containing only YYYY-MM-DD | >select from_iso8601_date("2019-11-05T09:09:16+08:00") 2019-11-05 |
Time grouping functions
Function description: Perform grouped aggregation statistics on log data at fixed time intervals, for example, counting the access counts every 5 minutes and other scenarios
Function format: histogram(time_column, interval)
Parameter description:
| Parameters | Description |
|---|---|
| time_column | Time column (KEY), for example @timestamp, whose value must be a unix timestamp of long type in milliseconds or a datetime expression of timestamp type. If the time column does not meet the above requirements, you can use the cast function to convert an ISO8601-formatted time string to the timestamp type, e.g., cast('2020-08-19T03:18:29.000Z' as timestamp) Note: When using timestamp for the time column, its corresponding datetime expression must be in UTC+0 timezone. If the datetime expression itself is in another timezone, it needs to be adjusted to UTC+0 through calculation. For example, when the original time is Beijing Time (UTC+8), adjust using cast('2020-08-19T03:18:29.000Z' as timestamp) - interval 8 hour. |
| interval | Specify fixed time intervals, with supported units including second, minute, hour, day, week, and month. For example, a 5-minute interval would be written as "interval 5 minute." |
Example:
Count PV values of access counts every 5 minutes: select histogram(cast(@timestamp as timestamp),interval 5 minute) as t,count(*) group by t order by t
Conditional functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| if(Boolean testCondition, T valueTrue, T valueFalseOrNull) | T | If the test condition is true, return ValueTrue; otherwise, return ValueFalseOrNull | >select if(2>1, 1, 0) 1 |
| nullif(T a, T b) | T | If a = b, return Null; otherwise, return a | >select nullif(1, 1) null |
| coalesce(T v1, T v2, ...) | T | Return the first non-Null value; if all parameters are Null, return Null | >select coalesce(null, 0, false, 1) 0 |
| CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | T | If a = b, return c; if a = d, return e; otherwise return f | >select case substring("abc", 1, 1) when "a" then "a" when "b" then "b" else "c" end a |
| CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | T | If a = true, return b; if c = true, return d; otherwise return e | >select case when substring("abc", 1, 1) = "a" then "a" when 2 > 1 then "b" else "c" end a |
Year-on-year and month-on-month functions
| Function signature | Response value | Description | Example |
|---|---|---|---|
| compare(x, t1, t2...) | array<float> | Compare the calculation results in the current time window with those in the time window before t1, t2.....seconds. The format of return value is [ current calculation result, calculation result before t second, and ratio of current calculation result to calculation result before T second ]. |
>select compare(x, 3600) from ( select avg(latency) as x ) [0.3,0.6,0.5] |
| ts_compare(x,t1, t2...) | array<float> | When applying timestamps, compare the calculation results in the current time window with those in the time window before t1, t2.....seconds. The format of return value is [ current calculation result, calculation result before t second, and ratio of current calculation result to calculation result before T second ]. The grouped timestamp (e.g., 2019-11-01 10:00) of the calculation result for the corresponding time window t1 (e.g., 3600) will automatically fill the time to (2019-11-01 11:00) and calculate the ratio with the current timestamp |
>select time, ts_compare(x, 3600) from ( select time, avg(latency) as x group by time ) 2019-11-01 12:00,[0.4,0.2,2] 2019-11-01 11:00, [0.3,0.6,0.5] |
Appendix
Date format
date_format supported by time functions
| Placeholder | Description |
|---|---|
| %a | Abbreviated weekday name (Sun..Sat) |
| %b | Abbreviated month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %H | Hour (00..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %i | Minutes, numeric (00..59) |
| %j | Day of year (001..366) |
| %k | Hour (0..23) |
| %l | Hour (1..12) |
| %M | Month name (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal % character |
Keywords
#
_binary
_utf8mb4
A - F
accessible action add against all alter analyze and as asc asensitive auto_increment before begin between bigint binary bit blob bool boolean both by call cascade case cast change char character charset check collate collation column columns comment commit committed condition constraint continue convert create cross current_date current_time current_timestamp current_user cursor database databases date datetime day_hour day_microsecond day_minute day_second dec decimal declare default delayed delete desc describe descriptor deterministic distinct distinctrow div double drop duplicate each else elseif enclosed end engines enum escape escaped exists exit expansion explain false fetch fields float float4 float8 flush for force foreign from full fulltext
G - N
generated geometry geometrycollection get global grant group group_concat having high_priority hour_microsecond hour_minute hour_second if ignore in index infile inner inout insensitive insert int int1 int2 int3 int4 int8 integer interval into io_after_gtids is isolation iterate join json json_extract key key_block_size keys kill language last_insert_id leading leave left less level like limit linear lines linestring load localtime localtimestamp lock long longblob longtext loop low_priority master_bind match maxvalue mediumblob mediumint mediumtext middleint minute_microsecond minute_second mod mode modifies multilinestring multipoint multipolygon names natural nchar next no no_write_to_binlog not null numeric
O - S
off offset on only optimize optimizer_costs option optionally or order out outer outfile partition plugins point polygon precision primary procedure processlist query read read_write reads real references regexp release rename reorganize repair repeat repeatable replace require resignal restrict return revoke right rlike rollback schema schemas second_microsecond select sensitive separator serializable session set share show signal signed smallint spatial specific sql sql_big_result sql_cache sql_calc_found_rows sql_no_cache sql_small_result sqlexception sqlstate sqlwarning ssl start starting status stored straight_join stream string substr substring
T - Z
table tables terminated text than then time timestamp timestampadd timestampdiff tinyblob tinyint tinytext to trailing transaction trigger true truncate uncommitted undo union unique unlock unsigned update usage use using utc_date utc_time utc_timestamp values varbinary varchar varcharacter variables varying view vindex vindexes virtual vitess_keyspaces vitess_shards vitess_tablets vitess_target vschema vschema_tables warnings when where while with write xor year year_month zerofill