Name | Description | Syntax |
Creates a datetime based on the provided year, month, day, hour, minute, and second values | DATETIME(year, month, day, hour, minute, second) | |
Creates a date based on the provided year, month, and day values | DATE(year, month, day) | |
Adds a specified offset of time (e.g., seconds, minutes, hours) to a given date or datetime | DATE_ADD(date, offset, "second/minute/hour/day/month/year") | |
Calculates the difference in days, weeks, months, quarters, or years between two dates | DATE_DIFF(end_date, start_date, "second/minute/hour/day/week/month/quarter/year") | |
Retrieves the day of the month from a given date or datetime | DAY(date) | |
Determines the day of the week for a given date or datetime, where Monday is 1, Tuesday is 2, and so on. | DAY_OF_WEEK(date, ["monday/tuesday/wednesday/thursday/friday/saturday/sunday"]) | |
Calculates the day of the year for a given date or datetime | DAY_OF_YEAR(date) | |
Calculates the number of days in the month for a given date or datetime | DAYS_IN_MONTH(date) | |
Calculates the number of days in a year for a given date or datetime, considering leap years | DAYS_IN_YEAR(date) | |
Extracts the hour component from a given datetime | HOUR(datetime) | |
Adds a specified number of days, weeks, months, quarters, or years to a date | date + INTERVAL number DAY/MONTH/YEAR | |
Adds a specified number of seconds, minutes, or hours to a datetime | datetime + INTERVAL number SECOND/MINUTE/HOUR | |
Extracts the minute component from a given datetime | MINUTE(datetime) | |
Retrieves the month component from a given date | MONTH(date) | |
Retrieves the current date and time | NOW() | |
Retrieves the quarter of the year from a given date or datetime | QUARTER(date) | |
Extracts the second component from a given datetime | SECOND(datetime) | |
Returns a standard time period (e.g., day, week, month, quarter, year) for a given date or datetime field | TIME_PERIOD(date, "day"/"week"/"month"/"quarter"/"year") | |
Calculates the number of time periods between a given date and the current period | TIME_PERIOD_OFFSET(date, "day"/"week"/"month"/"quarter"/"year"/"decade") | |
Retrieves the current date | TODAY() | |
Determines the week number of the year for a given date or datetime | WEEK(date) | |
Retrieves the year component from a given date or datetime | YEAR(date) |
Name | Description | Syntax |
Converts a value to a boolean (true/false) format | TO_BOOLEAN(value) | |
Converts text to a datetime format | TO_DATETIME(text) | |
Converts text to a date format | TO_DATE(text) | |
Converts a value to an integer | TO_INTEGER(value) | |
Converts a value to a numerical format | TO_NUMBER(value) | |
Converts a value, whether it's a datetime or any other data type, to a text format | TO_TEXT(value), TO_TEXT(datetime, “[format]”) |
Name | Description | Syntax |
Evaluates conditions and returns a value based on the first true condition | CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END | |
Checks if a value exists within a specified set of values | IN (...,...) |
Name | Description | Syntax |
Returns a value corresponding to the first true condition | IFS(condition1, value1, condition2, value2, ‚…,…) | |
Returns one value if a condition is true and another if false | IF(condition, value when true, value when false) |
Name | Description | Syntax |
Returns the absolute value of a number, ignoring its sign (+/-) | ABS(number) | |
Rounds a number up to the nearest integer | CEIL(number) | |
Rounds a number down to the nearest integer | FLOOR(number) | |
Calculates the distance between two points | GEO_DISTANCE(lat1, long1, lat2, long2, [unit]) | |
Returns the greatest value among the given columns (i.e., arguments) | GREATEST(arg1, arg2, ...) | |
Returns the smallest value among the given columns (i.e., arguments) | LEAST(arg1, arg2, ...) | |
Calculates the logarithm of a number to a specified base | LOG(number, base) | |
Returns the remainder of a division operation | MOD(dividend, divisor) | |
Raises a number to the power of another number | POWER(number, exponent) | |
Generates a random decimal value between 0 and 1 | RANDOM() | |
Rounds a number down to the nearest integer | ROUNDDOWN(number) | |
Rounds a number up to the nearest integer | ROUNDUP(number) | |
Rounds a number to a specified number of decimal places | ROUND(number, [decimals]) |
Name | Description | Syntax |
Calculates the average value of numbers | AVG(number) | |
Combines all text values | CONCAT(text) | |
Combines unique text values | CONCAT_DISTINCT(text) | |
Counts the number of values | COUNT(arg) | |
Counts the distinct number of values | COUNT_DISTINCT(arg) | |
Calculates the running sum | CUMULATIVE_SUM(number) | |
Populates missing values for values above or below the row | FILL(arg) | |
Returns the first-row value | FIRST(arg) | |
Returns the last-row value | LAG(arg) | |
Returns the previous-row value | LAST(arg) | |
Returns the next-row value | LEAD(arg) | |
Returns the maximum value | MAX(arg) | |
Returns the median value | MEDIAN(number) | |
Returns the minimum value | MIN(arg) | |
Assigns a bucket number based on the number of tiles requested | NTILE(arg) | |
Assigns a rank to each row with ties receiving the same rank | RANK() | |
Assigns a row number to each row with ties receiving different values | ROW_NUM() | |
Calculates the standard deviation of numbers | STDDEV(number) | |
Calculates the sum of numbers | SUM(number) | |
Calculates the variance of numbers | VAR(number) |
Name | Description | Syntax |
Returns the first non-null value from a list of options. | COALESCE(option1, option2, ,…) | |
Concatenates multiple text text values into a single text | CONCAT(text1, text2, ‚…) | |
Determines whether a specified subtext exists within a text | CONTAINS(text, subtext) | |
Returns the first non-empty value from a list of options. | FIRST_NOT_EMPTY(option1, option2, ...) | |
Computes the hash value of a text using a specified algorithm (MD5 or SHA-1) | HASH(”md5”/”sha1”, text) | |
Escapes HTML special characters in text | ESCAPE_HTML(text) | |
Checks if a value or field is empty | IS_EMPTY(arg) | |
Extracts a specific field from JSON data | JSON_FIELD(json, json_path) | |
Sets a value for a specific field in JSON data | JSON_SET(json, json_path, value) | |
Encodes a JSON Web Token using a specified algorithm, key, and payload, with an optional custom header | JWT_Encode(algorithm, key, payload_json_text, [header_json_text] | |
Retrieves the leftmost characters from text | LEFT_PART(text, number) | |
Computes the length (number of characters) of text | LENGTH(text) | |
Measures the edit distance (Levenshtein distance) between two texts | LEVENSHTEIN(text1, text2) | |
Converts text to all lowercase characters | LOWER(text) | |
Extracts a portion of text starting from a specified position with a given length | MID(text, start, [length]) | |
Add padding characters to the left side of text, ensuring it reaches a specified length | PAD_LEFT(text, length, character) | |
Add padding characters to the right side of text, ensuring it reaches a specified length | PAD_RIGHT(text, length, character) | |
Capitalizes the first letter of each word in text | PROPER(text) | |
Extracts text that matches a regular expression pattern | REGEX_EXTRACT(text, “pattern", [offset]) | |
Checks if text matches a specified regular expression pattern | REGEX_MATCH(text, “pattern") | |
Replaces text that matches a regular expression pattern with a specified replacement | REGEX_REPLACE(text, “pattern”, replacement) | |
Returns the index of the first character of the first matching subtext based on a regular expression pattern | REGEX_SEARCH(text, “pattern", [offset]) | |
Repeats a specified text a given number of times. | REPEAT(text, number) | |
Replaces all occurrences of a specified search text with a replacement text | REPLACE(text, search, replace) | |
Reverses the order of characters in text | REVERSE(text) | |
Extracts the rightmost characters of text up to a specified length | RIGHT_PART(text, number) | |
Searches for a specific subtext within text and returns the index of the first occurrence | SEARCH(text, search) | |
Converts text to a SOUNDEX code for phonetic matching | SOUNDEX(text) | |
Extracts a specific part of text using a delimiter | SPLIT_PART(text, delimiter, part) | |
Removes leading and trailing spaces | TRIM(text) | |
Converts HTML escape sequences in text back to their original characters | UNESCAPE_HTML(text) | |
Converts text to all uppercase characters | UPPER(text) |