Chapter 4. Built In Functions

Fred Toussi

The HSQL Development Group

$Revision: 6759 $

Copyright 2010-2024 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.

2024-06-01

Table of Contents

Overview
String and Binary String Functions
JSON Functions
Numeric Functions
Date Time and Interval Functions
Functions to Report the Time Zone.
Functions to Report the Current Datetime
Functions to Extract an Element of a Datetime
Functions for Datetime Arithmetic
Functions to Convert or Format a Datetime
Array Functions
General Functions
System Functions

Overview

HyperSQL supports a wide range of built-in functions and allows user-defined functions written in SQL and Java languages. User-defined functions are covered in the SQL-Invoked Routines chapter. If a built-in function is not available, you can write your own using procedural SQL or Java.

Built-in aggregate functions such as SUM, MAX, ARRAY_AGG, GROUP_CONCAT are covered in the Data Access and Change chapter, which covers SQL in general. SQL expressions such as COALESCE, NULLIF and CAST are also discussed there.

The built-in functions fall into three groups:

  • SQL Standard Functions

    A wide range of functions defined by SQL/Foundation are supported. SQL/Foundation functions that have no parameter are called without empty parentheses. Functions with multiple parameters often use keywords instead of commas to separate the parameters. Many functions are overloaded. Among these, some have one or more optional parameters that can be omitted, while the return type of some functions is dependent upon the type of one of the parameters. The usage of SQL Standard Functions (where they can be used) is covered more extensively in the Data Access and Change chapter

  • JDBC Open Group CLI Functions

    These functions were defined as an extension to the CLI standard, which is the basis for ODBC and JDBC and supported by many database products. JDBC supports an escape mechanism to specify function calls in SQL statements in a manner that is independent of the function names supported by the target database engine. For example SELECT {fn DAYOFMONTH (dateColumn)} FROM myTable can be used in JDBC and is translated to Standard SQL as SELECT EXTRACT (DAY_OF_MONTH FROM dateColumn) FROM myTable if a database engine supports the Standard syntax. If a database engine does not support Standard SQL, then the translation will be different. HyperSQL supports all the function names specified in the JDBC specifications as native functions. Therefore, there is no need to use the {fn FUNC_NAME ( ... ) } escape with HyperSQL. If a JDBC function is supported by the SQL Standard in a different form, the SQL Standard form is the preferred form to use.

  • HyperSQL Built-In Functions

    Many additional built-in functions are available for some useful operations. Some of these functions return the current setting for the session and the database. The General Functions accept arguments of different types and return values based on comparison between the arguments.

In the BNF specification used here, words in capital letters are actual tokens. Syntactic elements such as expressions are enclosed in angle brackets. The <left paren> and <right paren> tokens are represented with the actual symbol. Optional elements are enclosed with square brackets ( <left bracket> and <right bracket> ). Multiple options for a required element are enclosed with braces ( <left brace> and <right brace> ). Alternative tokens are separated with the vertical bar ( <vertical bar> ). At the end of each function definition, the standard which specifies the function is noted in parentheses as JDBC or HyperSQL, or the SQL:2023 Standard.

String and Binary String Functions

In SQL, there are three kinds of string: character, binary and bit. The units are respectively characters, octets, and bits. Each kind of string can be in different data types. CHAR, VARCHAR and CLOB are the character data types. BINARY, VARBINARY and BLOB are the binary data types. BIT and BIT VARYING are the bit string types. In all string functions, the position of a unit of the string within the whole string is specified from 1 to the length of the whole string. In the BNF, <char value expr> indicates any valid SQL expression that evaluates to a character type. Likewise, <binary value expr> indicates a binary type and <num value expr> indicates a numeric type.

ASCII

ASCII ( <char value expr> )

Returns an INTEGER equal to the ASCII code value of the first character of <char value expr>. (JDBC)

ASCIISTR

ASCIISTR ( <char value expr> )

Returns the ASCII representation of the string argument with all characters outside the range 32-126 replaced with Unicode escape codes. (HyperSQL)

BIT_LENGTH

BIT_LENGTH ( <string value expression> )

BIT_LENGTH can be used with character, binary and bit strings. It return a BIGINT value that measures the bit length of the string. (SQL:2023)

See also CHARACTER_LENGTH and OCTET_LENGTH.

CHAR

CHAR ( <UNICODE code> )

The argument is an INTEGER. Returns a character string containing a single character that has the specified <UNICODE code>, which is an integer. ASCII codes are a subset of the allowed values for <UNICODE code>. (JDBC)

CHARACTER_LENGTH

{ CHAR_LENGTH | CHARACTER_LENGTH } ( <char value expression> [ USING { CHARACTERS | OCTETS } ] )

The CHAR_LENGTH or CHARACTER_LENGTH function can be used with character strings, while OCTET_LENGTH can be used with character or binary strings and BIT_LENGTH can be used with character, binary and bit strings.

All functions return a BIGINT value that measures the length of the string in the given unit. CHAR_LENGTH counts characters, OCTET_LENGTH counts octets and BIT_LENGTH counts bits in the string. For CHAR_LENGTH, if [ USING OCTETS ] is specified, the octet count is returned, which is twice the normal length. (SQL:2023)

CONCAT

CONCAT ( <char value expr 1>, <char value expr 2> [, ...] )

CONCAT ( <binary value expr 1>, <binary value expr 2> [, ...] )

The arguments are character strings or binary strings. Returns a string formed by concatenation of the arguments. Minimum number of arguments is 2. Equivalent to the SQL concatenation expression <value expr 1> || <value expr 2> [ || ...] .

Handling of null values in the CONCAT function depends on the database property sql.concat_nulls ( SET DATABASE SQL SYNTAX CONCAT NULLS { TRUE || FALSE } ). By default, any null value will cause the function to return null. If the property is set false, then NULL values are replaced with empty strings.

(JDBC)

CONCAT_WS

CONCAT_WS ( <char value separator>, <char value expr 1>, <char value expr 2> [, ...] )

The arguments are character strings. Returns a string formed by concatenation of the arguments from the second argument, using the separator from the first argument. Minimum number of arguments is 3. Equivalent to the SQL concatenation expression <value expr 1> || <separator> || <value expr 2> [ || ...] . The function ignores null values and returns an empty string if all values are null. It returns null only if the separator is null.

This function is similar to a MySQL function of the same name.

(HyperSQL)

DIFFERENCE

DIFFERENCE ( <char value expr 1>, <char value expr 2> )

The arguments are character strings. Converts the arguments into SOUNDEX codes, and returns an INTEGER between 0-4 which indicates how similar the two SOUNDEX value are. If the values are the same, it returns 4, if the values have no similarity, it returns 0. In-between values are returned for partial similarity. (JDBC)

FROM_BASE64

FROM_BASE64( <character value expr> )

Returns a binary string by converting from the base64 <character value expr>. (HyperSQL)

INSERT

INSERT ( <char value expr 1>, <offset>, <length>, <char value expr 2> )

Returns a character string based on <char value expr 1> in which <length> characters have been removed from the <offset> position and in their place, the whole <char value expr 2> is copied. Equivalent to SQL/Foundation OVERLAY( <char value expr1> PLACING < char value expr2> FROM <offset> FOR <length> ) . (JDBC)

INSTR

INSTR ( <char value expr 1>, <char value expr 2> [ , <offset> ] )

Returns as a BIGINT value the starting position of the first occurrence of <char value expr 2> within <char value expr 1>. If <offset> is specified, the search begins with the position indicated by <offset>. If the search is not successful, 0 is returned. Similar to the LOCATE function but the order of the arguments is reversed. (HyperSQL)

HEX

HEX( <binary value expr> )

HEX( <numeric value expr> )

Returns a character string of hexadecimal digits and letters representing the <binary value expr>. Exactly the same as the RAWTOHEX function. With <numeric value expr> the hexadecimal digits represent the number in base 16 (HyperSQL)

HEXTORAW

HEXTORAW( <char value expr> )

Returns a binary string formed by translation of hexadecimal digits and letters in the <char value expr>. Each character of the <char value expr> must be a digit or a letter in the A | B | C | D | E | F set. Each byte of the retired binary string is formed by translating two hex digits into one byte. (HyperSQL)

LCASE

LCASE ( <char value expr> )

Returns a character string that is the lower-case version of the <char value expr>. Equivalent to SQL/Foundation LOWER (<char value expr>). (JDBC)

LEFT

LEFT ( <char value expr>, <count> )

Returns a character string consisting of the first <count> characters of <char value expr>. Equivalent to SQL/Foundation SUBSTRING(<char value expr> FROM 0 FOR <count>). (JDBC)

LENGTH

LENGTH ( <char value expr> )

Returns as a BIGINT value the number of characters in <char value expr>. Equivalent to SQL/Foundation CHAR_LENGTH(<char value expr>). (JDBC)

LOCATE

LOCATE ( <char value expr 1>, <char value expr 2> [ , <offset> ] )

Returns as a BIGINT value the starting position of the first occurrence of <char value expr 1> within <char value expr 2>. If <offset> is specified, the search begins with the position indicated by <offset>. If the search is not successful, 0 is returned. Without the third argument, LOCATE is equivalent to the SQL Standard function POSITION(<char value expr 1> IN <char value expr 2>). (JDBC)

LOWER

LOWER ( <char value expr> )

Returns a character string that is the lower-case version of the <char value expr>. (SQL:2023)

LPAD

LPAD ( <char value expr 1>, <length> [, <char value expr 2> ] )

Returns a character string with the length of <length> characters. The string contains the characters of <char value expr 1> padded to the left with spaces. If <length> is smaller than the length of the string argument, the argument is truncated. If the optional <char value expr 2> is specified, this string is used for padding, instead of spaces. (SQL:2023)

LTRIM

LTRIM ( <char value expr 1> [, <char value expr 2> ] )

When called with a single argument, returns a character string based on <char value expr 1> with the leading space characters removed. Equivalent to SQL/Foundation TRIM( LEADING ' ' FROM <char value expr1> ). When called with two arguments, <char value expr 2> represents the leading character to be removed. (SQL:2023)

OCTET_LENGTH

OCTET_LENGTH ( <string value expression> )

The OCTET_LENGTH function can be used with character or binary strings.

Return a BIGINT value that measures the length of the string in octets. When used with character strings, the octet count is returned, which is twice the normal length. (SQL:2023)

OVERLAY

OVERLAY ( <char value expr 1> PLACING <char value expr 2>

FROM <start position> [ FOR <string length> ] [ USING CHARACTERS ] )

OVERLAY ( <binary value expr 1> PLACING <binary value expr 2>

FROM <start position> [ FOR <string length> ] )

The character version of OVERLAY returns a character string based on <char value expr 1> in which <string length> characters have been removed from the <start position> and in their place, the whole <char value expr 2> is copied.

The binary version of OVERLAY returns a binary string formed in the same manner as the character version. (SQL:2023)

POSITION

POSITION ( <char value expr 1> IN <char value expr 2> [ USING CHARACTERS ] )

POSITION ( <binary value expr 1> IN <binary value expr 2> )

The character and binary versions of POSITION search the string value of the second argument for the first occurrence of the first argument string. If the search is successful, the position in the string is returned as a BIGINT. Otherwise zero is returned. (SQL:2023)

RAWTOHEX

RAWTOHEX( <binary value expr> )

Returns a character string composed of hexadecimal digits representing the bytes in the <binary value expr>. Each byte of the <binary value expr> is translated into two hex digits. (HyperSQL)

REGEXP_COUNT

REGEXP_COUNT ( <char value expr>, <regular expression> )

Returns as an INTEGER value the number of regions of the <char value expr> that match the <regular expression>. The <regular expression> is defined according to Java language regular expression rules. Returns 0 if no match is found. (HyperSQL)

REGEXP_INSTR

REGEXP_INSTR ( <char value expr>, <regular expression> )

Returns as an INTEGER value the starting position of the first region of the <char value expr> that matches the <regular expression>. The <regular expression> is defined according to Java language regular expression rules. Returns 0 if no match is found. (HyperSQL)

REGEXP_LIKE

REGEXP_LIKE ( <char value expr>, <regular expression> )

REGEXP_MATCHES

REGEXP_MATCHES ( <char value expr>, <regular expression> )

Both functions return true if the <char value expr> matches the <regular expression> as a whole. The <regular expression> is defined according to Java language regular expression rules. (HyperSQL)

REGEXP_REPLACE

REGEXP_REPLACE ( <char value expr>, <regular expression> [, <replace char value expr> [, <start position> [, <replace count> [, <options>]]]] )

Replaces <char value expr 1> regions that match the <regular expression> with <replace char value expr>. This last parameter is optional and defaults to the empty string. The rest of the parameters are also optional. The <start position> parameter is not implemented and must be 1 if used. The <replace count> parameter is 0 by default, which means replace all occurrences, or it can be 1, which means replace only the first occurrence. The <options> parameter is a string which can contain: 'i' for case-insensitive compare, 'c' for Unicode case, 'n' for the '.' also to match any line terminator, and 'm' for multi-line matches. The <regular expression> is defined according to Java language regular expression rules. (HyperSQL)

REGEXP_SUBSTR

REGEXP_SUBSTR ( <char value expr>, <regular expression> )

REGEXP_SUBSTRING

REGEXP_SUBSTRING ( <char value expr>, <regular expression> )

Both functions return the first region in the <char value expr> that matches the <regular expression>. The <regular expression> is defined according to Java language regular expression rules. (HyperSQL)

REGEXP_SUBSTRING_ARRAY

REGEXP_SUBSTRING_ARRAY ( <char value expr>, <regular expression> )

Returns all the regions in the <char value expr> that match the <regular expression>. The <regular expression> is defined according to Java language regular expression rules. Returns an array containing the matching regions (HyperSQL)

REPEAT

REPEAT ( <char value expr>, <count> )

Returns a character string based on <char value expr>, repeated <count> times. (JDBC)

REPLACE

REPLACE ( <char value expr 1>, <char value expr 2> [, <char value expr 3> ] )

Returns a character string based on <char value expr 1> where each occurrence of <char value expr 2> has been replaced with a copy of <char value expr 3>. If the function is called with just two arguments, the <char value expr 3> defaults to the empty string and calling the function simply removes the occurrences of <char value expr 2> from the first string.(JDBC)

REVERSE

REVERSE ( <char value expr> )

Returns a character string based on <char value expr> with characters in the reverse order. (HyperSQL)

RIGHT

RIGHT ( <char value expr>, <count> )

Returns a character string consisting of the last <count> characters of <char value expr>. (JDBC)

RPAD

RPAD ( <char value expr 1>, <length> [, <char value expr 2> ] )

Returns a character string with the length of <length> characters. The string begins with the characters of <char value expr 1> padded to the right with spaces. If <length> is smaller than the length of the string argument, the argument is truncated. If the optional <char value expr 2> is specified, this string is used for padding, instead of spaces. (SQL:2023)

RTRIM

RTRIM ( <char value expr 1> [, <char value expr 2> ] )

When called with a single argument, returns a character string based on <char value expr 1> with the trailing space characters removed. Equivalent to SQL/Foundation TRIM(TRAILING ' ' FROM <character string>). When called with two arguments, <char value expr 2> represents the trailing character to be removed. (SQL:2023)

SOUNDEX

SOUNDEX ( <char value expr> )

Returns a four-character code representing the sound of <char value expr>. The US census algorithm is used. For example, the soundex value for "Washington" is W252. This function is used for storing the soundex of names in a column of a table, then searching this column for strings matching the soundex of the search string, then comparing the actual names stored in the database with the search string. (JDBC)

SPACE

SPACE ( <count> )

Returns a character string consisting of <count> spaces. (JDBC)

SUBSTR

{ SUBSTR | SUBSTRING } ( <char value expr>, <offset>, <length> )

The JDBC version of SQL/Foundation SUBSTRING returns a character string that consists of <length> characters from <char value expr> starting at the <offset> position. (JDBC)

SUBSTRING

SUBSTRING ( <char value expr> FROM <start position> [ FOR <string length> ] [ USING CHARACTERS ] )

SUBSTRING ( <binary value expr> FROM <start position> [ FOR <string length> ] )

The character version of SUBSTRING returns a character string that consists of the characters of the <char value expr> from <start position>. If the optional <string length> is specified, only <string length> characters are returned.

The binary version of SUBSTRING returns a binary string in the same manner. (SQL:2023)

TO_BASE64

TO_BASE64( <binary value expr> )

Returns a character string as a base 64 representation of the bytes in the <binary value expr>. (HyperSQL)

TO_CHAR

TO_CHAR ( <Numeric value expr>, <char value expr> )

This function converts a numeric value to a character string using the format given in the second argument. In the format template, the digits 0 and 9 are used for the digits of the numeric value. If the numeric value has more significant digits than the template, the hash symbol (#) is returned as the result. By default, the result has the minus sign at the beginning when the numeric value is negative, or a space when it is positive. The special format element MI can be used at the end of the format template for a minus sign for negative numeric values or a space for positive values. The special format element S can be used at the beginning or end of the template for a plus or minus sign to be included for all numeric values at that position. Alternatively, the format element PR can be used once at the end of the format string for angle brackets around the negative results. Numeric values of type DOUBLE are first converted to DECIMAL with the same scale as indicated in the template. (HyperSQL)

Table 4.1. TO_CHAR (number) format elements

9The digit 9 means a space is used in the result when no digit is present. Otherwise the digit from the numeric value.
0

The digit 0 means the zero digit is used in the result when no digit is present. Otherwise the digit from the numeric value.

B

A blank string is returned if the numeric value is zero.

FM

Removes the initial spaces in the result.

MI

Space or minus sign at the end of the sequence of digits.

S

Plus or minus sign at the beginning or end of the sequence of digits.

PR

Angle brackets around the result are used for negative values.

.

The period character. Used for the decimal point.

,

The comma character. Used for grouping digits.


 TO_CHAR ( 1234567.890, '999,999,999.999' )
     1,234,567.890 

 TO_CHAR ( 1234567.890, '000,999,999.999' )
 001,234,567.890 

TRIM

TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim character> ] FROM ] <char value expr> )

TRIM ([ [ LEADING | TRAILING | BOTH ] [ <trim octet> ] FROM ] <binary value expr> )

The character version of TRIM returns a character string based on <char value expr>. Consecutive instances of <trim character> are removed from the beginning, the end or both ends of the<char value expr> depending on the value of the optional first qualifier [ LEADING | TRAILING | BOTH ]. If no qualifier is specified, BOTH is used as default. If [ <trim character> ] is not specified, the space character is used as default.

The binary version of TRIM returns a binary string based on <binary value expr>. Consecutive instances of <trim octet> are removed in the same manner as in the character version. If [ <trim octet> ] is not specified, the 0 octet is used as default. (SQL:2023)

TRANSLATE

TRANSLATE( <char value expr1>, <char value expr2>, <char value expr3> )

Returns a character string based on <char value expr1> source. Each character of the source is checked against the characters in <char value expr2>. If the character is not found, it is not modified. If the character is found, then the character in the same position in <char value expr3> is used. If <char value expr2> is longer than <char value expr3>, then those characters at the end that have no counterpart in <char value expr3> are dropped from the result. (HyperSQL)

 -- in this example any accented character in acolumn is replaced with one without an accent
 TRANSLATE( acolumn, 'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü', 'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu');

UCASE

UCASE ( <char value expr> )

Returns a character string that is the upper case version of the <char value expr>. Equivalent to SQL/Foundation UPPER( <char value expr> ). (JDBC)

UPPER

UPPER ( <char value expr> )

Returns a character string that is the upper case version of the <char value expr> . (SQL:2023)

UNHEX

UNHEX( <char value expr> )

Returns a binary string formed by translation of hexadecimal digits and letters in the <char value expr>. Exactly the same as the HEXTORAW function. (HyperSQL)

UNISTR

UNISTR( <char value expr> )

Returns a string formed by translation of hexadecimal escape sequences in the <char value expr> to UTF-16 characters. Exactly the opposite of ASCIISTR function. (HyperSQL)

JSON Functions

JSON constructor functions convert SQL data into JSON values, These functions are supported by HyperSQL according to the SQL:2023 Standard. Each function returns a JSON object or JSON array as a string. The format conforms to the IETF rfc:7159 document, The JavaScript Object Notation (JSON) Data Interchange Format.

In the BNF, <JSON value expr> indicates any valid SQL expression that evaluates to a single value. This includes strings, numbers and booleans. Binary values are represented as hexadecimal strings. All other types, including dates and timestamps, are represented as strings. An optional FORMAT JSON is used when the result of the value expression is already a string in the JSON format.

<JSON value expr> ::= <value expression> [ FORMAT JSON ]

The optional <JSON constructor null clause> indicates whether NULL values are represented as JSON nulls or are omitted from the result. The default behaviour is ABSENT ON NULL for JSON_ARRAY, and NULL ON NULL for JSON_OBJECT.

<JSON null clause> ::= NULL ON NULL | ABSENT ON NULL

The optional <JSON output clause> allows you to change the maximum length of the returned string from the default 32K bytes.

<JSON output clause> ::= RETURNING VARCHAR(N)

JSON function calls can be nested to construct more complex objects containing arrays, objects, and arrays of objects.

JSON_ARRAY

JSON_ARRAY ( <JSON value expr>, ... [<JSON null caluse>] [<JSON output caluse>])

The first form of JSON_ARRAY is a variable argument function, It converts the values from a series of value expression into a single JSON array.

JSON_ARRAY ( <query expression> [ FORMAT JSON ] [<JSON null caluse>] [<JSON output caluse>])

The second form of JSON_ARRAY converts the values from a single <query expression> into a JSON array. (SQL:2023)

-- a JSON array is constructed from a single row of the table

SELECT JSON_ARRAY( country_id , name, local_name ) FROM places.countries WHERE country_id = 'ESP'
 
C1                       
------------------------ 
["ESP","Spain","España"]

-- a JSON array is constructed from all rows of the table

VALUES JSON_ARRAY(SELECT name FROM countries)
  
C1                                                                    
--------------------------------------------------------------------- 
["Austria","Switzerland","Germany","Spain","France","Italy","Sweden"]

JSON_ARRAYAGG

JSON_ARRAYAGG ( <JSON value expr> [<order by clause>] [<JSON null caluse>] )

JSON_ARRAYAGG is an aggregate function similar to the ARRAY_AGG SQL function, It evaluates a value expression over a series of rows and combines the values, separated by commas, into a single JSON array. Returns a JSON string. (SQL:2023)

-- a JSON array is constructed from all rows of the table

SELECT JSON_ARRAYAGG( country_id ) FROM places.countries
 
C1                                          
------------------------------------------- 
["AUT","CHE","DEU","ESP","FRA","ITA","SWE"]

JSON_OBJECT

JSON_OBJECT ( <JSON name and value>, ... [<JSON null caluse>] [ { WITH | WITHOUT } UNIQUE [ KEYS] ] )

JSON_OBJECT is a variable argument function, It combines a series of keys and values into a single JSON object. Returns a JSON string.

There are two supported syntax forms for key:value pairs.

<JSON name and value> ::= <JSON name> <colon> <JSON value expression> | [ KEY ] <JSON name> VALUE <JSON value expression>

The optional WITH UNIQUE KEYS clause forces an error if the keys within the JSON object are not unique. The default is WITHOUT UNIQUE KEYS. (SQL:2023)

-- a JSON object is constructed from each row of the table; both syntax options are shown

SELECT country_id, JSON_OBJECT( country_id : name ) FROM places.countries

SELECT country_id, JSON_OBJECT( KEY country_id  VALUE name ) FROM places.countries
 
 
COUNTRY_ID C2                    
---------- --------------------- 
AUT        {"AUT":"Austria"}     
CHE        {"CHE":"Switzerland"} 
DEU        {"DEU":"Germany"}     
ESP        {"ESP":"Spain"}       
FRA        {"FRA":"France"}      
ITA        {"ITA":"Italy"}       
SWE        {"SWE":"Sweden"}

JSON_OBJECTAGG

JSON_OBJECTAGG ( <JSON name and value> [<JSON null caluse>] [ { WITH | WITHOUT } UNIQUE [ KEYS] ] )

JSON_OBJECTAGG is an aggregate function similar to the ARRAY_AGG SQL function, It constructs a JSON object by using keys and values from the aggregated rows. (SQL:2023)

-- a JSON object is constructed from multiple rows of the table

SELECT JSON_OBJECTAGG( country_id : name ) FROM places.countries
 
C1                                                                                                                                                 
--------------------------------------------------------------------------------------------------------------- 
{"AUT":"Austria","CHE":"Switzerland","DEU":"Germany","ESP":"Spain","FRA":"France","ITA":"Italy","SWE":"Sweden"}

Numeric Functions

ABS

ABS ( <num value expr> | <interval value expr> )

Returns the absolute value of the argument as a value of the same type. (SQL:2023)

ACOS

ACOS ( <num value expr> )

Returns the arc-cosine of the argument in radians as a value of DOUBLE type. (SQL:2023)

ASIN

ASIN ( <num value expr> )

Returns the arc-sine of the argument in radians as a value of DOUBLE type. (SQL:2023)

ATAN

ATAN ( <num value expr> )

Returns the arc-tangent of the argument in radians as a value of DOUBLE type. (SQL:2023)

ATAN2

ATAN2 ( <num value expr 1>, <num value expr 2> )

The <num value expr 1> and <num value expr 2> express the x and y coordinates of a point. Returns the angle, in radians, representing the angle coordinate of the point in polar coordinates, as a value of DOUBLE type. (JDBC)

CEILING

{ CEIL | CEILING } ( <num value expr> )

Returns the smallest integer greater than or equal to the argument. If the argument is exact numeric then the result is exact numeric with a scale of 0. If the argument is approximate numeric, then the result is of DOUBLE type. (SQL:2023)

BITAND

BITAND ( <num value expr 1>, <num value expr 2> )

BITAND ( <bit value expr 1>, <bit value expr 2> )

BITANDNOT

BITANDNOT ( <num value expr 1>, <num value expr 2> )

BITANDNOT ( <bit value expr 1>, <bit value expr 2> )

BITNOT

BITNOT ( <num value expr 1> )

BITNOT ( <bit value expr 1> )

BITOR

BITOR ( <num value expr 1>, <num value expr 2> )

BITOR ( <bit value expr 1>, <bit value expr 2> )

BITXOR

BITXOR ( <num value expr 1>, <num value expr 2> )

BITXOR ( <bit value expr 1>, <bit value expr 2> )

These functions perform bit operations on two values, or in the case of BITNOT on a single value. The values are either integer values, or bit strings. The result is an integer value of the same type as the arguments, or a bit string of the same length as the argument. Each bit of the result is formed by performing the operation on corresponding bits of the arguments. The names of the function indicate NOT, OR, AND, XOR operations. The BITANDNOT performs NOT on the second argument, then performs AND on result and the first argument. (HyperSQL)

COS

COS ( <num value expr> )

Returns the cosine of the argument (an angle expressed in radians) as a value of DOUBLE type. (SQL:2023)

COSH

COSH ( <num value expr> )

Returns the hyperbolic cosine of the argument as a value of DOUBLE type. (SQL:2023)

COT

COT ( <num value expr> )

Returns the cotangent of the argument as a value of DOUBLE type. The <num value expr> represents an angle expressed in radians. (SQL:2023)

DEGREES

DEGREES ( <num value expr> )

Converts the argument (an angle expressed in radians) into degrees and returns the value in the DOUBLE type. (JDBC)

EXP

EXP ( <num value expr> )

Returns the exponential value of the argument as a value of DOUBLE type. (SQL:2023)

FLOOR

FLOOR ( <num value expr> )

Returns the largest integer that is less than or equal to the argument. If the argument is exact numeric then the result is exact numeric with a scale of 0. If the argument is approximate numeric, then the result is of DOUBLE type. (SQL:2023)

LN

LN ( <num value expr> )

Returns the natural logarithm of the argument, as a value of DOUBLE type. (SQL:2023)

LOG

LOG ( <num value expr> )

Returns the natural logarithm of the argument, as a value of DOUBLE type. (JDBC)

LOG10

LOG10 ( <num value expr> )

Returns the base 10 logarithm of the argument as a value of DOUBLE type. (SQL:2023)

MOD

MOD ( <num value expr 1>, <num value expr 2> )

Returns the remainder (modulus) of <num value expr 1> divided by <num value expr 2>. The data type of the returned value is the same as the second argument. (SQL:2023)

NANVL

NANVL ( <num value expr 1>, <num value expr 2> )

Returns an alternative for the NaN (Not a Number) double value in <num value expr 1> as <num value expr 2>., otherwise returns the first argument. The data type of the returned value is DOUBLE. (HyperSQL)

PI

PI ()

Returns the constant pi as a value of DOUBLE type. (JDBC)

POWER

POWER ( <num value expr 1>, <num value expr 2> )

Returns the value of <num value expr 1> raised to the power of <int value expr 2> as a value of DOUBLE type. (SQL:2023)

RADIANS

RADIANS ( <num value expr> )

Converts the argument (an angle expressed in degrees) into radians and returns the value in the DOUBLE type. (JDBC)

RAND

RAND ( [ <int value expr> ] )

Returns a random value in the DOUBLE type. The optional [ <int value expr> ] is used as seed value. In HyperSQL each session has a separate random number generator. The first call that uses a seed parameter sets the seed for subsequent calls that do not include a parameter. (JDBC)

ROUND

ROUND ( <num value expr>, <int value expr> )

The <num value expr> is of the DOUBLE type or DECIMAL type. The function returns a DOUBLE or DECIMAL value which is the value of the argument rounded to <int value expr> places right of the decimal point. If <int value expr> is negative, the first argument is rounded to <int value expr> places to the left of the decimal point.

This function rounds values ending with .5 or larger away from zero for DECIMAL arguments and results. When the value ends with .5 or larger and the argument and result are DOUBLE, It rounds the value towards the closest even value.

The datetime version is discussed in the next section. (JDBC)

SIGN

SIGN ( <num value expr> )

Returns an INTEGER, indicating the sign of the argument. If the argument is negative then -1 is returned. If it is equal to zero then 0 is returned. If the argument is positive then 1 is returned. (JDBC)

SIN

SIN ( <num value expr> )

Returns the sine of the argument (an angle expressed in radians) as a value of DOUBLE type. (SQL:2023)

SINH

SINH ( <num value expr> )

Returns the hyperbolic sine of the argument as a value of DOUBLE type. (SQL:2023)

SQRT

SQRT ( <num value expr> )

Returns the square root of the argument as a value of DOUBLE type. (SQL:2023)

TAN

TAN ( <num value expr> )

Returns the tangent of the argument (an angle expressed in radians) as a value of DOUBLE type. (SQL:2023)

TANH

TANH ( <num value expr> )

Returns the hyperbolic tangent of the argument as a value of DOUBLE type. (SQL:2023)

TO_NUMBER

TO_NUMBER ( <char value expr> )

Performs a cast from character to DECIMAL number. The character string must consist of digits and can have a decimal point. Use the SQL Standard CAST expression instead of this non-standard function. (HyperSQL)

TRUNC

TRUNC ( <num value expr> [, <int value expr>] )

This is a similar to the TRUNCATE function when the first argument is numeric. If the second argument is omitted, zero is used in its place.

The datetime version is discussed in the next section. (HyperSQL)

TRUNCATE

TRUNCATE ( <num value expr> [, <int value expr>] )

Returns a value in the same type as <num value expr> but may reduce the scale of DECIMAL and NUMERIC values. The value is rounded by replacing digits with zeros from <int value expr> places right of the decimal point to the end. If <int value expr> is negative, ABS( <int value expr> ) digits to left of the decimal point and all digits to the right of the decimal points are replaced with zeros. Results of calling TRUNCATE with 12345.6789 with (-2, 0, 2, 4) are (12300, 12345, 12345.67, 12345.6789). The function does not change the number if the second argument is larger than or equal to the scale of the first argument.

If the second argument is not a constant (when it is a parameter or column reference) then the type of the return value is always the same as the type of the first argument. In this case, the discarded digits are replaced with zeros. (JDBC)

WIDTH_BUCKET

WIDTH_BUCKET ( <value expr 1> , <value expr 2>, <value expr 3>, <int value expr> )

Returns an integer value between 0 and <int value expr> + 1. The initial three parameters are of the same numeric or datetime type. The range, ( <value expr 2> , <value expr 3> ) is divided into <int value expr> equal sections (buckets). The returned integer value indicates the index of the bucket where <value expr 1> can be placed. If the <value expr 1> falls before or after the range, the return value is 0 or <value expr 1> + 1 respectively.

This function can be used with numeric or datetime values. Invalid arguments, including <int value expr> smaller than 1, or equal values for <value expr 2> and <value expr 3> will cause an exception. (SQL:2023)

An example is given below:

 WIDTH_BUCKET( 5, 10, 110, 10)
 0

 WIDTH_BUCKET( 23, 10, 110, 10)
 2

 WIDTH_BUCKET( 100, 10, 110, 10)
 10

 WIDTH_BUCKET( 200, 10, 110, 10)
 11

Date Time and Interval Functions

Functions to report the time zone.

Functions to Report the Time Zone.

TIMEZONE

TIMEZONE()

Returns the current time zone displacement for the session. This value is the same as SESSION_TIMEZONE if the user has not changed the TIME ZONE of the session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)

SESSION_TIMEZONE

SESSION_TIMEZONE()

Returns the initial (default) time zone displacement for the current session. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)

SESSIONTIMEZONE

SESSIONTIMEZONE()

Returns the same value as SESSION_TIMEZONE. (HyperSQL)

DATABASE_TIMEZONE

DATABASE_TIMEZONE()

Returns the time zone displacement for the database engine. This is based on where the database server process is located. Returns an INTERVAL HOUR TO MINUTE value. (HyperSQL)

DBTIMEZONE

DBTIMEZONE()

Similar to DATABASE_TIMEZONE. Returns a string. Works in ORA compatibility mode only.(HyperSQL)

Functions to Report the Current Datetime

CURRENT_DATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIME [ ( <time precision> ) ]

LOCALTIME

LOCALTIME [ ( <time precision> ) ]

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP [ ( <timestamp precision> ) ]

LOCALTIMESTAMP

LOCALTIMESTAMP [ ( <timestamp precision> ) ]

These datetime functions return the datetime value representing the moment the function is called. CURRENT_DATE returns a value of DATE type. CURRENT_TIME returns a value of TIME WITH TIME ZONE type with the time zone of the SQL session. LOCALTIME returns a value of TIME type. CURRENT_TIMESTAMP returns a value of TIMESTAMP WITH TIME ZONE type with the time zone of the SQL session. LOCALTIMESTAMP returns a value of TIMESTAMP type. If the optional [ ( <time precision> ) ] or [ ( <timestamp precision> ) ] is used, then the returned value has the specified fraction of the second precision. When the functions are used multiple times in a single SQL statement, the returned values represent the same point of time.

From version 2.7.0 the functions return values with microsecond precision. Previous versions returned values with millisecond precision. (SQL:2023)

NOW

NOW ()

This function is equivalent to LOCALTIMESTAMP. It can be used as a no-arg function as the parens are optional. (HyperSQL)

CURDATE

CURDATE ()

This function is equivalent to CURRENT_DATE. (JDBC)

CURTIME

CURTIME ()

This function is equivalent to LOCALTIME. (JDBC)

SYSDATE

SYSDATE

This no-arg function is similar to LOCALTIMESTAMP but it returns the timestamp without fraction of second. (HyperSQL)

SYSTIMESTAMP

SYSTIMESTAMP

This no-arg function is similar to CURRENT_TIMESTAMP and is enabled in ORA syntax mode only. It returns the timestamp when it is called. (HyperSQL)

TODAY

TODAY

This no-arg function is equivalent to CURRENT_DATE. (HyperSQL)

Functions to Extract an Element of a Datetime

DATENAME, DATEPART and EOMONTH

These functions are available in the MSS compatibility mode and perform the equivalent of EXTRACT function or the LAST_DAY function. (HyperSQL)

DAYNAME

DAYNAME ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_NAME FROM ... ) Returns a string in the range of Sunday - Saturday. (JDBC)

DAYOFMONTH

DAYOFMONTH ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_OF_MONTH FROM ... ) Returns an integer value in the range of 1-31. (JDBC)

DAYOFWEEK

DAYOFWEEK ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_OF_WEEK FROM ... ) Returns an integer value in the range of 1-7. The first day of the week is Sunday. (JDBC)

DAYOFYEAR

DAYOFYEAR ( <datetime value expr> )

This function is equivalent to EXTRACT ( DAY_OF_YEAR FROM ... ) Returns an integer value in the range of 1-366. (JDBC)

DAYS

DAYS ( <datetime value expr> )

The <datetime value expr> is of DATE or TIMESTAMP type. This function returns the DAY number since the first day of the calendar. The first day is numbered 1. (HyperSQL)

HOUR

HOUR ( <datetime value expr> )

This function is equivalent to EXTRACT ( HOUR FROM ... ) Returns an integer value in the range of 0-23. (JDBC)

MINUTE

MINUTE ( <datetime value expr> )

This function is equivalent to EXTRACT ( MINUTE FROM ... ) Returns an integer value in the range of 0 - 59. (JDBC)

MONTH

MONTH ( <datetime value expr> )

This function is equivalent to EXTRACT ( MONTH FROM ... ) Returns an integer value in the range of 1-12. (JDBC)

MONTHNAME

MONTHNAME ( <datetime value expr> )

This function is equivalent to EXTRACT ( NAME_OF_MONTH FROM ... ) Returns a string in the range of January - December. (JDBC)

QUARTER

QUARTER ( <datetime value expr> )

This function is equivalent to EXTRACT ( QUARTER FROM ... ) Returns an integer in the range of 1 - 4. (JDBC)

SECOND

SECOND ( <datetime value expr> )

This function is equivalent to EXTRACT ( SECOND FROM ... ) Returns a decimal in the range of 0 - 60, with the same precision as the <datetime value expr>. (JDBC)

SECONDS_SINCE_MIDNIGHT

SECONDS_SINCE_MIDNIGHT ( <datetime value expr> )

This function is equivalent to EXTRACT ( SECONDS_SINCE_MIDNIGHT FROM ... ) Returns an integer in the range of 0 - 86399. (HyperSQL)

UNIX_MILLIS

UNIX_MILLIS ( [ <datetime value expression> ] )

This function returns a BIGINT value. With no parameter, it returns the number of milliseconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of milliseconds since 1970-01-01. (HyperSQL)

UNIX_TIMESTAMP

UNIX_TIMESTAMP ( [ <datetime value expression> ] )

This function returns a BIGINT value. With no parameter, it returns the number of seconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of seconds since 1970-01-01. See also the TIMESTAMP ( <num value expression> ) function to return a TIMESTAMP from a Unix timestamp. (HyperSQL)

WEEK

WEEK ( <datetime value expr> )

This function is equivalent to EXTRACT ( WEEK_OF_YEAR FROM ... ) Returns an integer in the range of 1 - 54. (JDBC)

YEAR

YEAR ( <datetime value expr> )

This function is equivalent to EXTRACT ( YEAR FROM ... ) Returns an integer in the range of 1 - 9999. (JDBC)

EXTRACT

EXTRACT ( <extract field> FROM <extract source> )

<extract field> ::= YEAR | MONTH | DAY | HOUR | MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR | QUARTER | DAY_OF_YEAR | DAY_OF_MONTH |

TIMEZONE_HOUR | TIMEZONE_MINUTE | SECOND | SECONDS_SINCE_MIDNIGHT |

DAY_NAME | MONTH_NAME

<extract source> ::= <datetime value expr> | <interval value expr>

The EXTRACT function returns a field or element of the <extract source>. The <extract source> is a datetime or interval expression. The type of the return value is BIGINT for most of the <extract field> options. The exception is SECOND, where a DECIMAL value is returned which has the same precision as the datetime or interval expression. The field values DAY_NAME or MONTH_NAME result in a character string. When MONTH_NAME is specified, a string in the range January - December is returned. When DAY_NAME is specified, a string in the range Sunday -Saturday is returned.

If the <extract source> is FROM <datetime value expr>, different groups of <extract source> can be used depending on the data type of the expression. The TIMEZONE_HOUR | TIMEZONE_MINUTE options are valid only for TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE data types. The HOUR | MINUTE | SECOND | SECONDS_MIDNIGHT options, are valid for TIME and TIMESTAMP types. The rest of the fields are valid for DATE and TIMESTAMP types.

If the <extract source> is FROM <interval value expr>, the <extract field> must be one of the fields of the INTERVAL type of the expressions. The YEAR | MONTH options may be valid for INTERVAL types based on months. The DAY | HOUR | MINUTE | SECOND | SECONDS_MIDNIGHT options may be valid for INTERVAL types based on seconds. For example, DAY | HOUR | MINUTE are the only valid fields for the INTERVAL DAY TO MINUTE data type. (SQL:2023 with HyperSQL extensions)

Functions for Datetime Arithmetic

NEXT_DAY

NEXT_DAY ( <datetime value expr>, <character value expr>)

This function returns a TIMESTAMP for compatibility reasons. The return value is the next weekday named by the second argument that occurs after the first date. For example, next Wednesday is expressed as NEXT_DAY(CURRENT_DATE, 'WEDNESDAY'). (HyperSQL)

ADD_MONTHS

ADD_MONTHS ( <datetime value expr>, <numeric value expr>)

This function is similar but different to simple addition of a MONTH interval to a datetime value. The SQL Standard expression, <datetime value expr> + n MONTH, when used with the last day of a short month such as February, returns a date that has the same day of the month in the target month. The ADD_MONTHS function adjusts the target day to the last day of the target month. For all other days, the behaviour is the same. This function always returns a TIMESTAMP(0) value, regardless of the type of the argument. (HyperSQL)

The example below compares the output of the function and the expression.

 VALUES ADD_MONTHS ( DATE '2012-02-29' , 1), DATE '2012-02-29' + 1 MONTH

 C1                  C2         
 ------------------- ---------- 
 2012-03-31 00:00:00 2012-03-29 

LAST_DAY

LAST_DAY ( <datetime value expr> )

Returns the last day of the month for the given <datetime value expr>. The returned value preserves the year, month, hour, minute and second fields of the timestamp. The type of the result is always TIMESTAMP(0). (HyperSQL)

 VALUES LAST_DAY ( TIMESTAMP '2012-02-14 12:30:44')

 C1                  
 ------------------- 
 2012-02-29 12:30:44 

MONTHS_BETWEEN

MONTHS_BETWEEN ( <datetime value expr1> , <datetime value expr2> )

Returns a number (not an INTERVAL) possibly with a fraction, representing the number of months between two days. If both dates have the same day of month, or are on the last day of the month, the result is an exact numeric. Otherwise, the fraction is calculated based on 31 days per month. You can cast the resulting value into INTERVAL MONTH and use it for datetime arithmetic. (HyperSQL)

 VALUES MONTHS_BETWEEN ( TIMESTAMP '2013-02-14 12:30:44', TIMESTAMP '2012-01-04 12:30:44')

 C1                                  
 ----------------------------------- 
 13.32258064516129000000000000000000 

TIMESTAMPADD

TIMESTAMPADD ( <tsi datetime field>, <numeric value expression>, <datetime value expr>)

TIMESTAMPDIFF

TIMESTAMPDIFF ( <tsi datetime field>, <datetime value expr 1>, <datetime value expr 2>)

<tsi datetime field> ::= SQL_TSI_FRAC_SECOND | SQL_TSI_MILLI_SECOND | SQL_TSI_SECOND | SQL_TSI_MINUTE | SQL_TSI_HOUR | SQL_TSI_DAY | SQL_TSI_WEEK | SQL_TSI_MONTH | SQL_TSI_QUARTER | SQL_TSI_YEAR

HyperSQL supports full SQL Standard datetime features. It supports adding integers representing units of time directly to datetime values using the arithmetic plus operator. It also supports subtracting one <datetime value expr> from another in the given units of date or time using the minus operator. An example of <datetime value expr> + <numeric value expression> <datetime field> is LOCALTIMESTAMP + 5 DAY. An example of ( <datetime value expr> - <numeric value expression> ) <datetime field> is (CURRENT_DATE - DATE '2008-08-8') MONTH which returns the number of calendar months between the two dates.

The two JDBC functions, TIMESTAMPADD and TIMESTAMPDIFF perform a similar function to the above SQL expressions. The <tsi datetime field> names are keywords and are different from those used in the EXTRACT functions. These names are valid for use only when calling these two functions. With TIMESTAMPDIFF, the names indicate the unit of time used to compute the difference between two datetime fields. With TIMESTAMPADD they represent the unit of time used for the <numeric value expression>. The unit of time for each name is self-explanatory. In the case of SQL_TSI_FRAC_SECOND, the unit is nanosecond.

The type of the return value for TIMESTAMPADD is the same as the type of the datetime argument used, except when the argument type is DATE and a unit smaller than DAY is added the return type is TIMESTAMP. The return type for TIMESTAMPDIFF is always BIGINT, regardless of the type of arguments. The two datetime arguments of TIMESTAMPDIFF should be of the same type. The TIME type is not supported for the arguments to these functions.

TIMESTAMPDIFF is evaluated as <datetime value expr 2> - <datetime value expr 1>. (JDBC)

 TIMESTAMPADD ( SQL_TSI_MONTH, 3, DATE '2008-11-22' )

 TIMESTAMPDIFF ( SQL_TSI_HOUR, TIMESTAMP '2008-11-20 20:30:40', TIMESTAMP '2008-11-21 21:30:40' )

DATE_ADD

DATE_ADD ( <datetime value expr> , <interval value expr> )

DATE_SUB

DATE_SUB ( <datetime value expr> , <interval value expr> )

These functions are equivalent to arithmetic addition and subtraction, <datetime value expr> + <interval value expr> and <datetime value expr> - <interval value expr>. The functions are provided for compatibility with other databases. The supported interval units are the standard SQL interval unit listed in other chapters of this guide. The TIME type is supported for the argument to these functions. (HyperSQL)

 DATE_ADD ( DATE '2008-11-22', INTERVAL 3 MONTH )

 DATE_SUB ( TIMESTAMP '2008-11-22 20:30:40', INTERVAL 20 HOUR )

DATEADD

DATEADD ( <field>, <numeric value expr>, <datetime value expr> )

DATEDIFF

DATEDIFF ( <field>, <datetime value expr 1>, <datetime value expr 2> )

<field> ::= 'yy' | 'year' | 'qq' | 'quarter' |'mm' | 'month' | 'dd' | 'day' | 'hh' | 'hour' | 'mi' | 'minute' | 'ss' | 'second' | 'ms' | 'millisecond' | 'mcs' | 'microsecond' | 'ns' | 'nanosecond'

<field> ::= YY | YEAR | QQ | QUARTER | MM | MONTH | DD | DAY | HH | HOUR | MI | MINUTE | SS | SECOND | MS | MILLISECOND | MCS | MICROSECOND | NS | NANOSECOND

The DATEADD and DATEDIFF functions are alternatives to TIMESTAMPADD and TIMESTAMPDIFF, with similar field options. The field names are specified as strings or as keywords. The short field names translate to YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND and NANOSECOND. DATEDIFF is evaluated as <datetime value expr 2> - <datetime value expr 1>. (HyperSQL}

DATEDIFF ( <datetime value expr 1>, <datetime value expr 2> )

This special form of DATEDIFF does not have a field parameter and return the number of days between two dates. This form is evaluated as <datetime value expr 1> - <datetime value expr 2>, which is different from the main form. This form is compatible with some other database engines. The TIME type is not supported for the arguments to these functions. (HyperSQL}

 DATEADD ( 'month', 3, DATE '2008-11-22' )

 DATEDIFF ( 'hour', TIMESTAMP '2008-11-22 20:30:40', TIMESTAMP '2008-11-22 00:30:40' )

ROUND

ROUND ( <datetime value expr> [ , <char value expr> ] )

The <datetime value expr> is of DATE, TIME or TIMESTAMP type. The <char value expr> is one of the format strings: 'YYYY', 'YY', 'MONTH', 'MM', 'WW', 'IW', 'DDD', 'DD', 'HH24', 'HH12', 'HH', 'MI', 'SS'; represeting YEAR, MONTH, WEEK OF YEAR, ISO WEEK, DAY, HOUR, MINUTE or SECOND as listed in the table for TO_CHAR and TO_DATE format elements (see below). The datetime value is rounded up or down after the specified field and the rest of the fields to the right are set to the minimum value (one for MONTH and DAY, or zero, for the rest of the fields). For example, rounding a timestamp value on the DAY field results in midnight the same date or midnight the next day if the time is at or after 12 noon. If the second argument is omitted, the datetime value is rounded to the nearest day. (HyperSQL)

TRUNC

TRUNC ( <datetime value expr> [ , <char value expr> ] )

Similar to the ROUND function, the datetime value is truncated after the specified field and the rest of the fields to the right are set to the minimum value (one for MONTH and DAY, or zero, for the rest of the fields). For example, applying TRUNC to a timestamp value on the DAY field results in midnight the same date. Examples of ROUND and TRUNC functions are given below. If the second argument is omitted, the datetime value is truncated to midnight the same date. (HyperSQL)

 ROUND ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

 TIMESTAMP '2009-01-01 00:00:00'

 TRUNC ( TIMESTAMP'2008-08-01 20:30:40', 'YYYY' )

 TIMESTAMP '2008-01-01 00:00:00'

Functions to Convert or Format a Datetime

FROM_TZ

FROM_TZ ( <timestamp value expr>, <zone or interval spec string> )

This function takes the date-time (year, month, day, hour, minute, second, fraction) from the first argument and the time zone from the second argument to construct a TIMESTAMP WITH TIME ZONE value. The output can represent a different point of universal time (UTC) than the input. This is different from the AT TIME ZONE expression which keeps the UTC value and changes the zone. (HyperSQL)

-- the output shows the same timestamp as the input, but with the time zone of the specified region
VALUES FROM_TZ(TIMESTAMP'2022-03-28 11:00:00+4:00','America/Chicago')
 C1                              
 ------------------------------- 
 2022-03-28 11:00:00.000000-5:00 

-- this example has the same output as the previous one
VALUES FROM_TZ(TIMESTAMP'2022-03-28 11:00:00','-5:00')

NUMTODSINTERVAL

NUMTODSINTERVAL ( <numeric value expr>, <interval spec string> )

This function converts the numeric value to an interval, exactly like CAST. The interval spec string is one of 'DAY', 'HOUR', 'MINUTE', 'SECOND'. (HyperSQL)

NUMTOYMINTERVAL

NUMTOYMINTERVAL ( <numeric value expr>, <interval spec string> )

This function converts the numeric value to an interval, exactly like CAST. The interval spec string is 'YEAR' or 'MONTH'. (HyperSQL)

TIMESTAMP

TIMESTAMP ( <num value expr> )

TIMESTAMP ( <char value expr> )

TIMESTAMP ( <char value expr>, <char value expr> )

TIMESTAMP ( <date value expr>, <time value expr> )

This function translates the arguments into a TIMESTAMP WITHOUT TIME ZONE value.

When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.

When the single argument is a formatted date or timestamp string, it is translated to a TIMESTAMP.

When two arguments are used, the first argument is the DATE part and the second argument is the TIME part of the returned TIMESTAMP value. The types of the arguments can be DATE and TIME respectively, or they can be any character string type. An example, including the result, is given below:

 TIMESTAMP ( '2008-11-22', '20:30:40' )

 TIMESTAMP '2008-11-22 20:30:40.000000'

TIMESTAMP_WITH_ZONE

TIMESTAMP_WITH_ZONE ( <num value expr> )

TIMESTAMP_WITH_ZONE ( <char value expr> )

This function translates the arguments into a TIMESTAMP WITH TIME ZONE value.

When the single argument is a numeric value, it is interpreted as a Unix timestamp in seconds.

When the single argument is TIMESTAMP, it is converted to TIMESTAMP WITH TIME ZONE.

The time zone of the returned value is the local time zone at the time of the timestamp argument. This accounts for daylight saving times. For example, if the local time zone was +4:00 at the time of the given Unix timestamp, the returned value is local timestamp at the time with time zone +4:00.

TO_CHAR

TO_CHAR ( <datetime value expr>, <char value expr> )

This function formats a datetime value to the format given in the second argument. See TO_TIMESTAMP below for the details of the format string. Format elements such as DAY or HH12 are used in the examples below, with the result included.

Separator characters (space, comma, period, hyphen, colon, semicolon, forward slash) can be included between the pattern elements. You can include a string literal inside the format string by enclosing it in double quotes (see the second example above). Unsupported format characters should not be used. (HyperSQL)

 TO_CHAR ( TIMESTAMP'2008-02-01 20:30:40', 'YYYY BC DAY DD MONTH HH12 AM' )
 2008 AD Friday 01 February 08 pm 

 TO_CHAR ( DATE'2018-03-01', '"in" YYYY", on" DD MONTH' )
 in 2018, on 01 March

TO_DATE

TO_DATE ( <char value expr>, <char value expr> )

TO_TIMESTAMP

TO_TIMESTAMP ( <char value expr>, <char value expr> )

TO_TIMESTAMP_TZ

TO_TIMESTAMP ( <char value expr>, <char value expr> )

These functions translate a formatted datetime string to a TIMESTAMP. TO_DATE returns a TIMESTAMP(0) value with no sub-second fraction. TO_TIMESTAMP returns a TIMESTAMP(6). TO_TIMESTAMP_TZ returns a TIMESTAMP(6) WITH TIME ZONE value. The format string can contain pattern elements from the list given below, plus punctuation and space characters. The pattern should contain all the necessary fields to construct a date, including, year, month, day of month, etc. The returned timestamp can then be cast into DATE if necessary. Two examples together with the result are given below:

 TO_TIMESTAMP ( '22/11/2008 20:30:40', 'DD/MM/YYYY HH:MI:SS' )
 TO_TIMESTAMP ( '22/11/2008 20:30:40', 'JAVA:dd/MM/uuuu HH:mm:ss' ) // Java format

 TIMESTAMP '2008-11-22 20:30:40.000000'

The format strings that can be used for TO_DATE and TO_TIMESTAMP are more restrictive than those used for TO_CHAR, because the format string must contain the elements needed to build a full DATE or TIMESTAMP value. For example, you cannot use the 'WW', 'W', 'HH12' format elements.

The format is internally translated to a java.time.format.DateTimeFormatter format string. Unsupported format strings should not be used. With TO_CHAR, you can include a string literal inside the format string by enclosing it in double quotes.

From version 2.7.3 Java format strings as defined by java.time.format.DateTimeFormatter can be used directly as an alternative. These format strings must have the 'JAVA:' prefix as in the second example above, and the literals inside the format string must be enclosed in double single quotes as required by Java DateTimeFormatter.(HyperSQL)

The supported format components are all uppercase as follows:

Table 4.2. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements

BC | B.C. | AD | A.D.Returns AD for common era.
RRRR

4-digit year

YYYY

4-digit year

IYYY

4-digit year, corresponding to ISO week of the year. The reported year for the last few days of the calendar year may be the next year.

YY

2 digit year

IY

2 digit year, corresponding to ISO week of the year

MM

Month (01-12)

MON

Short three-letter name of month

MONTH

Name of month

IW

Week of year (1-52 or 1-53) based on the ISO standard. Week starts on Monday. The first week may start near the end of previous year.

W

Week of month (0-5) where the first day of the month is in week 1, or in week 0 for an ISO week at the beginning of the calendar year which starts in the previous week-based year. Week starts on Monday.

DAY

Name of day.

DD

Day of month (01-31).

DDD

Day of year (1-366).

DY

Short three-letter name of day.

HH12

Hour of day (0-11).

AM | A.M. | PM | P.M.

Meridian indicator.

HH

Hour of day (00-23).

HH24

Hour of day (00-23).

MI

Minute (00-59).

SS

Second (00-59).

FF[1-9]

Fractional seconds. With TO_CHAR, 'FF' indicates six digits are written. 'FF' followed by a single digit writes the indicated number of digits. For example 'FF2' means 2 digits after the fraction. With TO_TIMESTAMP, 'FF' indicates a variable number of up to 9 digits are read.

TZ

Time zone hours and minutes.


Array Functions

Array functions are specialised functions with ARRAY parameters or return values. For the ARRAY_AGG aggregate function, see the Data Access and Change chapter.

CARDINALITY

CARDINALITY( <array value expr> )

Returns the element count for the given array argument. (SQL:2023)

MAX_CARDINALITY

MAX_CARDINALITY( <array value expr> )

Returns the maximum allowed element count for the given array argument. (SQL:2023)

POSITION_ARRAY

POSITION_ARRAY( <value expression> IN <array value expr> [ FROM <int value expr> ] )

Returns the position of the first match for the <value expression> in the array. By default, the search starts from the beginning of the array. The optional <int value expr> specifies the start position. Positions are counted from 1. Returns zero if no match is found. (HyperSQL)

SORT_ARRAY

SORT_ARRAY( <array value expr> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )

Returns a sorted copy of the array. By default, sort is performed in ascending order and NULL elements are sorted first. (HyperSQL)

TRIM_ARRAY

TRIM_ARRAY( <array value expr>, <num value expr> )

Returns a new array that contains the elements of the <array value expr> minus the number of elements specified by the <num value expr>. Elements are discarded from the end of the array. (SQL:2023)

SEQUENCE_ARRAY

SEQUENCE_ARRAY( <value expr 1>, <value expr 2>, <value expr 3 )

Returns a new array that contains a sequence of values. The <value expr 1> is the lower bound of the range. The <value expr 2> is the upper bound of the range. The <value expr 3> is the increment. The elements of the array are within the inclusive range. The first element is <value expr 1> and each subsequent element is the sum of the previous element and the increment. If the increment is zero, only the first element is returned. When the increment is negative, the lower bound should be larger than the upper bound. The type of the arguments can be all number types, or a datetime range and an interval for the third argument (HyperSQL)

In the examples below, a number sequence and a date sequence are shown. The UNNEST table expression is used to form a table from the array.

 SEQUENCE_ARRAY(0, 100, 5)

 ARRAY[0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100] 

 SELECT * FROM UNNEST(SEQUENCE_ARRAY(10, 12, 1))

 C1 
 -- 
 10 
 11 
 12 

 SELECT * FROM UNNEST(SEQUENCE_ARRAY(CURRENT_DATE, CURRENT_DATE + 6 DAY, 1 DAY)) WITH ORDINALITY AS T(D, I) 

 D          I 
 ---------- - 
 2010-08-01 1 
 2010-08-02 2 
 2010-08-03 3 
 2010-08-04 4 
 2010-08-05 5 
 2010-08-06 6 
 2010-08-07 7

General Functions

General functions can take different types of arguments. Some General Functions accept a variable number of arguments.

Also see the Data Access and Change chapter for SQL expressions that are similar to functions, for example CAST and NULLIF.

CASEWHEN

CASEWHEN( <boolean value expr>, <value expr 2>, <value expr 3> )

If the <boolean value expr> is true, returns <value expr 2> otherwise returns <value expr 3>. Use a CASE WHEN expression instead for more extensive capabilities and options. CASE WHEN is documented in the Data Access and Change chapter. (HyperSQL)

COALESCE

COALESCE( <value expr 1>, <value expr 2> [, ...] )

Returns <value expr 1> if it is not null, otherwise returns <value expr 2> if not null and so on. The type of both arguments must be comparable. (SQL:2023)

CONVERT

CONVERT ( <value expr> , <data type> )

<data type> ::= { SQL_BIGINT | SQL_BINARY | SQL_BIT |SQL_BLOB | SQL_BOOLEAN | SQL_CHAR | SQL_CLOB | SQL_DATE | SQL_DECIMAL | SQL_DATALINK |SQL_DOUBLE | SQL_FLOAT | SQL_INTEGER | SQL_LONGVARBINARY | SQL_LONGNVARCHAR | SQL_LONGVARCHAR | SQL_NCHAR | SQL_NCLOB | SQL_NUMERIC | SQL_NVARCHAR | SQL_REAL | SQL_ROWID | SQL_SQLXML | SQL_SMALLINT | SQL_TIME | SQL_TIMESTAMP | SQL_TINYINT | SQL_VARBINARY | SQL_VARCHAR} [ ( <precision, length or scale parameters> ) ]

The CONVERT function is a JDBC escape function, equivalent to the SQL standard CAST expression. It converts the <value expr> into the given <data type> and returns the value. The <data type> options are synthetic names made by prefixing type names with SQL_. Some of the <data type> options represent valid SQL types, but some are based on non-standard type names, namely { SQL_LONGNVARCHAR | SQL_LONGVARBINARY |SQL_LONGVARCHAR | SQL_TINYINT }. None of the synthetic names can be used in any other context than the CONVERT function.

The definition of CONVERT in the JDBC Standard does not allow the precision, scale or length to be specified. This is required by the SQL standard for BINARY, BIT, BLOB, CHAR, CLOB, VARBINARY and VARCHAR types and is often needed for DECIMAL and NUMERIC. Defaults are used for precision.

HyperSQL also allows the use of real type names (without the SQL_ prefix). In this usage, HyperSQL allows the use of precision, scale or length for the type definition when they are valid for the type definition.

When MS SQL Server compatibility mode is on, the parameters of CONVERT are switched and only the real type names with required precision, scale or length are allowed. (JDBC)

DECODE

DECODE( <value expr main>, <value expr match 1>, <value expr result 1> [...,] [, <value expr default>] )

DECODE takes at least 3 arguments. The <value expr main> is compared with <value expr match 1> and if it matches, <value expr result 1> is returned. If there are additional pairs of <value expr match n> and <value expr result n>, comparison is repeated until a match is found the result is returned. If no match is found, the <value expr default> is returned if it is specified, otherwise NULL is returned. The type of the return value is a combination of the types of the <value expr result ... > arguments. (HyperSQL)

GREATEST

GREATEST( <value expr 1>, [<value expr ...>, ...] )

The GREATEST function takes one or more arguments. It compares the arguments with each other and returns the greatest argument. Arguments can be of any type, so long as they are comparable. The return type is the combined type of the arguments. For example comparing an integer value with a double value will return the greatest value as a DOUBLE. (SQL:2023)

IFNULL

ISNULL

IFNULL | ISNULL ( <value expr 1>, <value expr 2> )

Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is the type of <value expr 1>. Almost equivalent to SQL Standard COALESCE(<value expr 1>, <value expr 2>) function, but without type aggregation. (JDBC)

LEAST

LEAST( <value expr 1>, [<value expr ...>, ...] )

The LEAST function takes one or more arguments. It compares the arguments with each other and returns the smallest argument. Arguments can be of any type, so long as they are comparable. The return type is the combined type of the arguments. For example comparing an integer value with a double value will return the smallest value as a DOUBLE. (SQL:2023)

LOAD_FILE

LOAD_FILE ( <char value expr 1> [, <char value expr 2>] )

Returns a BLOB or CLOB containing the URL or file path specified in the first argument. If used with a single argument, the function returns a BLOB. If used with two arguments, the function returns a CLOB and the second argument is the character encoding of the file.

The file path is interpreted the same way as a TEXT TABLE source file location. The hsqldb.allow_full_path system property must be set true in order to access files outside the directory structure of the database files.

(HyperSQL)

NULLIF

NULLIF( <value expr 1>, <value expr 2> )

Returns <value expr 1> if it is not equal to <value expr 2>, otherwise returns null. The type of both arguments must be the same. This function is a shorthand for a specific CASE expression. (SQL:2023)

NVL

NVL( <value expr 1>, <value expr 2> )

Returns <value expr 1> if it is not null, otherwise returns <value expr 2>. The type of the return value is the type of <value expr 1>. For example, if <value expr 1> is an INTEGER column and <value expr 2> is a DOUBLE constant, the return type is cast into INTEGER. This function is similar to IFNULL. (HyperSQL)

NVL2

NVL2( <value expr 1>, <value expr 2>, <value expr 3> )

If <value expr 1> is not null, returns <value expr 2>, otherwise returns <value expr 3>. The type of the return value is the type of <value expr 2> unless it is null. (HyperSQL)

UUID

UUID ( [ { <char value expr> | <binary value expr> } ] )

With no parameter, this function returns a new UUID value as a 16-byte binary value in the UUID type. With a UUID hexadecimal string argument, it returns the 16-byte binary value in UUID. With a 16-byte binary or UUID argument, it returns the formatted UUID character representation. Note UUID is a type derived from BINARY(16) that in represented as a hexadecimal character string with the required hyphens. (HyperSQL)

NEWID

NEWID ()

This is a synonym for the no-arg UUID function in MSS compatibility mode. (HyperSQL)

SYS_GUID

SYS_GUID ()

Returns a UUID value as a 16 byte binary value in ORA compatibility mode. (HyperSQL)

System Functions

CRYPT_KEY

CRYPT_KEY( <value expr 1>, <value expr 2> )

Returns a binary string representation of a random cryptography key for the given cipher and cryptography provider. The cipher specification is specified by <value expr 1> and the provider by <value expr 2>. To use the default provider, specify null for <value expr 2>. (HyperSQL)

DIAGNOSTICS

DIAGNOSTICS ( ROW_COUNT )

This is a convenience function for use instead of the GET DIAGNOSTICS ... statement. The argument specifies the name of the diagnostics variable. Currently the only supported variable is the ROW_COUNT variable. The function returns the row count returned by the last executed statement. The return value is 0 after most statements. Calling this function immediately after executing an INSERT, UPDATE, DELETE or MERGE statement returns the row count for the last statement, as it is returned by the JDBC statement. (HyperSQL)

IDENTITY

IDENTITY ()

Returns the last IDENTITY value inserted into a row by the current session. The statement, CALL IDENTITY() can be made after an INSERT statement that inserts a row into a table with an IDENTITY column. The CALL IDENTITY() statement returns the last IDENTITY value that was inserted into a table by the current session. Each session manages this function call separately and is not affected by inserts in other sessions. The statement can be executed as a direct statement or a prepared statement. (HyperSQL)

DATABASE

DATABASE ()

Returns the file name (without directory information) of the database. (JDBC)

DATABASE_NAME

DATABASE_NAME ()

Returns the database name. This name is a 16-character, uppercase string. It is generated as a string based on the timestamp of the creation of the database, for example HSQLDB32438AEAFB. The name can be redefined by an admin user but the new name must be all uppercase and 16 characters long. This name is used in log messages with external logging frameworks. (HyperSQL)

DATABASE_VERSION

DATABASE_VERSION ()

Returns the full version string for the database engine. For example, 2.7.3. (JDBC)

USER

USER ()

Equivalent to the SQL function CURRENT_USER. (JDBC)

CURRENT_USER

CURRENT_USER

CURRENT_ROLE

CURRENT_ROLE

SESSION_USER

SESSION_USER

SYSTEM_USER

SYSTEM_USER

CURRENT_SCHEMA

CURRENT_SCHEMA

CURRENT_CATALOG

CURRENT_CATALOG

These functions return the named current session attribute. They are all SQL Standard functions.

The CURRENT_USER is the user that connected to the database, or a user subsequently set by the SET AUTHORIZATION statement.

SESSION_USER is the same as CURRENT_USER

SYSTEM_USER is the user that connected to the database. It is not changed with any command until the session is closed.

CURRENT_SCHEMA is default schema of the user, or a schema subsequently set by the SET SCHEMA command.

CURRENT_CATALOG is always the same within a given HyperSQL database and indicates the name of the catalog.

IS_AUTOCOMMIT

IS_AUTOCOMMIT()

Returns TRUE if the session is in auto-commit mode. (HyperSQL)

IS_READONLY_SESSION

IS_READONLY_SESSION()

Returns TRUE if the session is in read only mode. (HyperSQL)

IS_READONLY_DATABASE

IS_READONLY_DATABASE()

Returns TRUE if the database is a read only database. (HyperSQL)

IS_READONLY_DATABASE_FILES

IS_READONLY_DATABASE_FILES()

Returns TRUE if the database is a read-only files database. In this kind of database, it is possible to modify the data, but the changes are not persisted to the database files. (HyperSQL)

ISOLATION_LEVEL

ISOLATION_LEVEL()

Returns the current transaction isolation level for the session. Returns either READ COMMITTED or SERIALIZABLE as a string. (HyperSQL)

SESSION_ID

SESSION_ID()

Returns the id of the session as a BIGINT value. Each session id is unique during the operational lifetime of the database. Id's are restarted after a shutdown and restart. (HyperSQL)

SESSION_ISOLATION_LEVEL

SESSION_ISOLATION_LEVEL()

Returns the default transaction isolation level for the current session. Returns either READ COMMITTED or SERIALIZABLE as a string. (HyperSQL)

DATABASE_ISOLATION_LEVEL

DATABASE_ISOLATION_LEVEL()

Returns the default transaction isolation level for the database. Returns either READ COMMITTED or SERIALIZABLE as a string. (HyperSQL)

TRANSACTION_SIZE

TRANSACTION_SIZE()

Returns the row change count for the current transaction. Each row change represents a row INSERT or a row DELETE operation. There will be a pair of row change operations for each row that is updated.

TRANSACTION_ID

TRANSACTION_ID()

Returns the current transaction ID for the session as a BIGINT value. The database maintains a global incremental id which is allocated to new transactions and new actions (statement executions) in different sessions. This value is unique to the current transaction. (HyperSQL)

TRANSACTION_UTC

TRANSACTION_UTC()

Returns the transaction timestamp in UTC time zone for the session. This timestamp is used in updates made to system-versioned tables during the transaction. (HyperSQL)

ACTION_ID

ACTION_ID()

Returns the current action ID for the session as a BIGINT value. The database maintains a global incremental id which is allocated to new transactions and new actions (statement executions) in different sessions. This value is unique to the current action. (HyperSQL)

TRANSACTION_CONTROL

TRANSACTION_CONTROL()

Returns the current transaction model for the database. Returns LOCKS, MVLOCKS or MVCC as a string. (HyperSQL)

LOB_ID

LOB_ID( <column reference> )

Returns internal ID of a lob as a BIGINT value. Lob ID's are unique and never reused. The <column reference> is the name of the column (or variable, or argument) which is a CLOB or BLOB. Returns null if the value is null. (HyperSQL)

ROWNUM

ROWNUM()

ROW_NUMBER

ROW_NUMBER() OVER()

Returns the current row number (from 1) being processed in a select statement. This has the same semantics as the ROWNUM pseudo-column in ORA syntax mode, but can be used in any syntax mode. The function is used in a SELECT or DELETE statement. The ROWNUM of a row is incremented as the rows are added to the result set. It is therefore possible to use a condition such as WHERE ROWNUM() < 10, but not ROWNUM() > 10 or ROWNUM = 10. The ROW_NUMBER() OVER() alternative performs the same function and is included for compatibility with other database engines.(HyperSQL)


$Revision: 6752 $