SQL Functions
HarperDB encourages developers to utilize other querying tools over SQL for performance purposes. HarperDB SQL is intended for data investigation purposes and uses cases where performance is not a priority. SQL optimizations are on our roadmap for the future.
HarperDB SQL Functions
This SQL keywords reference contains the SQL functions available in HarperDB.
Functions
Aggregate
AVG
AVG(expression)
Returns the average of a given numeric expression.
COUNT
SELECT COUNT(column_name) FROM database.table WHERE condition
Returns the number records that match the given criteria. Nulls are not counted.
GROUP_CONCAT
GROUP_CONCAT(expression)
Returns a string with concatenated values that are comma separated and that are non-null from a group. Will return null when there are non-null values.
MAX
SELECT MAX(column_name) FROM database.table WHERE condition
Returns largest value in a specified column.
MIN
SELECT MIN(column_name) FROM database.table WHERE condition
Returns smallest value in a specified column.
SUM
SUM(column_name)
Returns the sum of the numeric values provided.
ARRAY*
ARRAY(expression)
Returns a list of data as a field.
DISTINCT_ARRAY*
DISTINCT_ARRAY(expression)
When placed around a standard ARRAY() function, returns a distinct (deduplicated) results set.
*For more information on ARRAY() and DISTINCT_ARRAY() see this blog.
Conversion
CAST
CAST(expression AS datatype(length))
Converts a value to a specified datatype.
CONVERT
CONVERT(data_type(length), expression, style)
Converts a value from one datatype to a different, specified datatype.
Date & Time
CURRENT_DATE
CURRENT_DATE()
Returns the current date in UTC in “YYYY-MM-DD” String format.
CURRENT_TIME
CURRENT_TIME()
Returns the current time in UTC in “HH:mm:ss.SSS” string format.
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
Referencing this variable will evaluate as the current Unix Timestamp in milliseconds. For more information, go here.
DATE
DATE([date_string])
Formats and returns the date_string argument in UTC in ‘YYYY-MM-DDTHH:mm:ss.SSSZZ’ string format. If a date_string is not provided, the function will return the current UTC date/time value in the return format defined above. For more information, go here.
DATE_ADD
DATE_ADD(date, value, interval)
Adds the defined amount of time to the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted interval values: Either string value (key or shorthand) can be passed as the interval argument. For more information, go here.
DATE_DIFF
DATEDIFF(date_1, date_2[, interval])
Returns the difference between the two date values passed based on the interval as a Number. If an interval is not provided, the function will return the difference value in milliseconds. For more information, go here.
DATE_FORMAT
DATE_FORMAT(date, format)
Formats and returns a date value in the String format provided. Find more details on accepted format values in the moment.js docs. For more information, go here.
DATE_SUB
DATE_SUB(date, format)
Subtracts the defined amount of time from the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted date_sub interval values- Either string value (key or shorthand) can be passed as the interval argument. For more information, go here.
DAY
DAY(date)
Return the day of the month for the given date.
DAYOFWEEK
DAYOFWEEK(date)
Returns the numeric value of the weekday of the date given(“YYYY-MM-DD”).NOTE: 0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, and 6=Saturday.
EXTRACT
EXTRACT(date, date_part)
Extracts and returns the date_part requested as a String value. Accepted date_part values below show value returned for date = “2020-03-26T15:13:02.041+000” For more information, go here.
GETDATE
GETDATE()
Returns the current Unix Timestamp in milliseconds.
GET_SERVER_TIME
GET_SERVER_TIME()
Returns the current date/time value based on the server’s timezone in YYYY-MM-DDTHH:mm:ss.SSSZZ String format.
OFFSET_UTC
OFFSET_UTC(date, offset)
Returns the UTC date time value with the offset provided included in the return String value formatted as YYYY-MM-DDTHH:mm:ss.SSSZZ. The offset argument will be added as minutes unless the value is less than 16 and greater than -16, in which case it will be treated as hours.
NOW
NOW()
Returns the current Unix Timestamp in milliseconds.
HOUR
HOUR(datetime)
Returns the hour part of a given date in range of 0 to 838.
MINUTE
MINUTE(datetime)
Returns the minute part of a time/datetime in range of 0 to 59.
MONTH
MONTH(date)
Returns month part for a specified date in range of 1 to 12.
SECOND
SECOND(datetime)
Returns the seconds part of a time/datetime in range of 0 to 59.
YEAR
YEAR(date)
Returns the year part for a specified date.
Logical
IF
IF(condition, value_if_true, value_if_false)
Returns a value if the condition is true, or another value if the condition is false.
IIF
IIF(condition, value_if_true, value_if_false)
Returns a value if the condition is true, or another value if the condition is false.
IFNULL
IFNULL(expression, alt_value)
Returns a specified value if the expression is null.
NULLIF
NULLIF(expression_1, expression_2)
Returns null if expression_1 is equal to expression_2, if not equal, returns expression_1.
Mathematical
ABS
ABS(expression)
Returns the absolute value of a given numeric expression.
CEIL
CEIL(number)
Returns integer ceiling, the smallest integer value that is bigger than or equal to a given number.
EXP
EXP(number)
Returns e to the power of a specified number.
FLOOR
FLOOR(number)
Returns the largest integer value that is smaller than, or equal to, a given number.
RANDOM
RANDOM(seed)
Returns a pseudo random number.
ROUND
ROUND(number,decimal_places)
Rounds a given number to a specified number of decimal places.
SQRT
SQRT(expression)
Returns the square root of an expression.
String
CONCAT
CONCAT(string_1, string_2, ...., string_n)
Concatenates, or joins, two or more strings together, resulting in a single string.
CONCAT_WS
CONCAT_WS(separator, string_1, string_2, ...., string_n)
Concatenates, or joins, two or more strings together with a separator, resulting in a single string.
INSTR
INSTR(string_1, string_2)
Returns the first position, as an integer, of string_2 within string_1.
LEN
LEN(string)
Returns the length of a string.
LOWER
LOWER(string)
Converts a string to lower-case.
REGEXP
SELECT column_name FROM database.table WHERE column_name REGEXP pattern
Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null.
REGEXP_LIKE
SELECT column_name FROM database.table WHERE REGEXP_LIKE(column_name, pattern)
Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null.
REPLACE
REPLACE(string, old_string, new_string)
Replaces all instances of old_string within new_string, with string.
SUBSTRING
SUBSTRING(string, string_position, length_of_substring)
Extracts a specified amount of characters from a string.
TRIM
TRIM([character(s) FROM] string)
Removes leading and trailing spaces, or specified character(s), from a string.
UPPER
UPPER(string)
Converts a string to upper-case.
Operators
Logical Operators
BETWEEN
SELECT column_name(s) FROM database.table WHERE column_name BETWEEN value_1 AND value_2
(inclusive) Returns values(numbers, text, or dates) within a given range.
IN
SELECT column_name(s) FROM database.table WHERE column_name IN(value(s))
Used to specify multiple values in a WHERE clause.
LIKE
SELECT column_name(s) FROM database.table WHERE column_n LIKE pattern
Searches for a specified pattern within a WHERE clause.
Queries
General
DISTINCT
SELECT DISTINCT column_name(s) FROM database.table
Returns only unique values, eliminating duplicate records.
FROM
FROM database.table
Used to list the database(s), table(s), and any joins required for a SQL statement.
GROUP BY
SELECT column_name(s) FROM database.table WHERE condition GROUP BY column_name(s) ORDER BY column_name(s)
Groups rows that have the same values into summary rows.
HAVING
SELECT column_name(s) FROM database.table WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s)
Filters data based on a group or aggregate function.
SELECT
SELECT column_name(s) FROM database.table
Selects data from table.
WHERE
SELECT column_name(s) FROM database.table WHERE condition
Extracts records based on a defined condition.
Joins
CROSS JOIN
SELECT column_name(s) FROM database.table_1 CROSS JOIN database.table_2
Returns a paired combination of each row from table_1 with row from table_2. Note: CROSS JOIN can return very large result sets and is generally considered bad practice.
FULL OUTER
SELECT column_name(s) FROM database.table_1 FULL OUTER JOIN database.table_2 ON table_1.column_name = table_2.column_name WHERE condition
Returns all records when there is a match in either table_1 (left table) or table_2 (right table).
[INNER] JOIN
SELECT column_name(s) FROM database.table_1 INNER JOIN database.table_2 ON table_1.column_name = table_2.column_name
Return only matching records from table_1 (left table) and table_2 (right table). The INNER keyword is optional and does not affect the result.
LEFT [OUTER] JOIN
SELECT column_name(s) FROM database.table_1 LEFT OUTER JOIN database.table_2 ON table_1.column_name = table_2.column_name
Return all records from table_1 (left table) and matching data from table_2 (right table). The OUTER keyword is optional and does not affect the result.
RIGHT [OUTER] JOIN
SELECT column_name(s) FROM database.table_1 RIGHT OUTER JOIN database.table_2 ON table_1.column_name = table_2.column_name
Return all records from table_2 (right table) and matching data from table_1 (left table). The OUTER keyword is optional and does not affect the result.
Predicates
IS NOT NULL
SELECT column_name(s) FROM database.table WHERE column_name IS NOT NULL
Tests for non-null values.
IS NULL
SELECT column_name(s) FROM database.table WHERE column_name IS NULL
Tests for null values.
Statements
DELETE
DELETE FROM database.table WHERE condition
Deletes existing data from a table.
INSERT
INSERT INTO database.table(column_name(s)) VALUES(value(s))
Inserts new records into a table.
UPDATE
UPDATE database.table SET column_1 = value_1, column_2 = value_2, ...., WHERE condition
Alters existing records in a table.
Last updated