Functions
Aggregate Functions
AVGBOOL_AND
BOOL_OR
COUNT
GROUP_CONCAT
MAX
MIN
SUM
SELECTIVITY
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP
Numeric Functions
ABSACOS
ASIN
ATAN
COS
COT
SIN
TAN
ATAN2
BITAND
BITOR
BITXOR
MOD
CEILING
DEGREES
EXP
FLOOR
LOG
LOG10
RADIANS
SQRT
PI
POWER
RAND
RANDOM_UUID
ROUND
ROUNDMAGIC
SECURE_RAND
SIGN
ENCRYPT
DECRYPT
HASH
TRUNCATE
COMPRESS
EXPAND
ZERO
String Functions
ASCIIBIT_LENGTH
LENGTH
OCTET_LENGTH
CHAR
CONCAT
DIFFERENCE
HEXTORAW
RAWTOHEX
INSTR
INSERT Function
LOWER
UPPER
LEFT
RIGHT
LOCATE
POSITION
LPAD
RPAD
LTRIM
RTRIM
TRIM
REGEXP_REPLACE
REPEAT
REPLACE
SOUNDEX
SPACE
STRINGDECODE
STRINGENCODE
STRINGTOUTF8
SUBSTRING
UTF8TOSTRING
XMLATTR
XMLNODE
XMLCOMMENT
XMLCDATA
XMLSTARTDOC
XMLTEXT
Time and Date Functions
CURRENT_DATECURRENT_TIME
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DAYNAME
DAY_OF_MONTH
DAY_OF_WEEK
DAY_OF_YEAR
EXTRACT
FORMATDATETIME
HOUR
MINUTE
MONTH
MONTHNAME
PARSEDATETIME
QUARTER
SECOND
WEEK
YEAR
System Functions
ARRAY_GETARRAY_LENGTH
AUTOCOMMIT
CANCEL_SESSION
CASEWHEN Function
CAST
COALESCE
CONVERT
CURRVAL
CSVREAD
CSVWRITE
DATABASE
DATABASE_PATH
FILE_READ
GREATEST
IDENTITY
IFNULL
LEAST
LOCK_MODE
LOCK_TIMEOUT
LINK_SCHEMA
MEMORY_FREE
MEMORY_USED
NEXTVAL
NULLIF
READONLY
ROWNUM
SCHEMA
SESSION_ID
SET
TABLE
USER
AVG
AVG([DISTINCT] {int | long | decimal | double}): value
The average (mean) value.
Aggregates are only allowed in select statements.
AVG(X)
BOOL_AND
BOOL_AND(boolean): boolean
Returns true if all expressions are true.
Aggregates are only allowed in select statements.
BOOL_AND(ID>10)
BOOL_OR
BOOL_OR(boolean): boolean
Returns true if any expression is true.
Aggregates are only allowed in select statements.
BOOL_OR(NAME LIKE 'W%')
COUNT
COUNT(*) | COUNT([DISTINCT] expression): int
The count of all row, or of the non-null values.
Aggregates are only allowed in select statements.
COUNT(*)
GROUP_CONCAT
GROUP_CONCAT([DISTINCT] string [ORDER BY {expression [ASC|DESC]}[,...]] [SEPARATOR expression]): string
Concatenates strings with a separator. The default separator is a ',' (without space).
Aggregates are only allowed in select statements.
GROUP_CONCAT(NAME ORDER BY ID SEPARATOR ', ')
MAX
MAX(value): value
The highest value.
Aggregates are only allowed in select statements.
MAX(NAME)
MIN
MIN(value): value
The lowest value.
Aggregates are only allowed in select statements.
MIN(NAME)
SUM
SUM([DISTINCT] {int | long | decimal | double}): value
The sum of all values.
Aggregates are only allowed in select statements.
SUM(X)
SELECTIVITY
SELECTIVITY(value): int
Estimates the selectivity (0-100) of a value.
The value is defined as (100 * distinctCount / rowCount).
The selectivity of 0 rows is 0 (unknown).
Up to 10000 values are kept in memory.
Aggregates are only allowed in select statements.
SELECT SELECTIVITY(FIRSTNAME), SELECTIVITY(NAME) FROM TEST WHERE ROWNUM()<100000
STDDEV_POP
STDDEV_POP([DISTINCT] double): double
The population standard deviation.
Aggregates are only allowed in select statements.
STDDEV_POP(X)
STDDEV_SAMP
STDDEV_SAMP([DISTINCT] double): double
The sample standard deviation.
Aggregates are only allowed in select statements.
STDDEV(X)
VAR_POP
VAR_POP([DISTINCT] double): double
The population variance (square of the population standard deviation).
Aggregates are only allowed in select statements.
VAR_POP(X)
VAR_SAMP
VAR_SAMP([DISTINCT] double): double
The sample variance (square of the sample standard deviation).
Aggregates are only allowed in select statements.
VAR_SAMP(X)
ABS
ABS({int | long | decimal | double}): value
See also Java Math.abs.
Please note that Math.abs(Integer.MIN_VALUE) == Integer.MIN_VALUE and
Math.abs(Long.MIN_VALUE) == Long.MIN_VALUE.
ABS(ID)
ACOS
ACOS(double): double
See also Java Math.* functions.
Example:ACOS(D)
ASIN
ASIN(double): double
See also Java Math.* functions.
Example:ASIN(D)
ATAN
ATAN(double): double
See also Java Math.* functions.
Example:ATAN(D)
COS
COS(double): double
See also Java Math.* functions.
Example:COS(ANGLE)
COT
COT(double): double
See also Java Math.* functions.
Example:COT(ANGLE)
SIN
SIN(double): double
See also Java Math.* functions.
Example:SIN(ANGLE)
TAN
TAN(double): double
See also Java Math.* functions.
Example:TAN(ANGLE)
ATAN2
ATAN2(double, double): double
See also Java Math.atan2.
Example:ATAN2(X, Y)
BITAND
BITAND(int, int): int
See also Java operator &.
Example:BITAND(A, B)
BITOR
BITOR(int, int): int
See also Java operator |.
Example:BITOR(A, B)
BITXOR
BITXOR(int, int): int
See also Java operator ^.
Example:BITXOR(A, B)
MOD
MOD(int, int): int
See also Java operator %.
Example:MOD(A, B)
CEILING
CEILING(double): double
See also Java Math.ceil.
Example:LOG(A)
DEGREES
DEGREES(double): double
See also Java Math.toDegrees.
Example:DEGREES(A)
EXP
EXP(double): double
See also Java Math.exp.
Example:EXP(A)
FLOOR
FLOOR(double): double
See also Java Math.floor.
Example:FLOOR(A)
LOG
LOG(double): double
See also Java Math.log.
Example:LOG(A)
LOG10
LOG10(double): double
See also Java Math.log10 (in Java 5).
Example:LOG10(A)
RADIANS
RADIANS(double): double
See also Java Math.toRadians.
Example:RADIANS(A)
SQRT
SQRT(double): double
See also Java Math.sqrt.
Example:SQRT(A)
PI
PI(): double
See also Java Math.PI.
Example:PI()
POWER
POWER(double, double): double
See also Java Math.pow.
Example:POWER(A, B)
RAND
RAND([int]): double
Calling the function without parameter returns the next a pseudo random number.
Calling it with an parameter seeds the session's random number generator.
RAND()
RANDOM_UUID
RANDOM_UUID(): UUID
Returns a new UUID with 122 pseudo random bits.
Example:RANDOM_UUID()
ROUND
ROUND(double, digitsInt): double
Rounds to a number of digits.
Example:ROUND(VALUE, 2)
ROUNDMAGIC
ROUNDMAGIC(double): double
This function rounds numbers in a good way but slow:
- special handling for numbers around 0
- only numbers <= +/-1000000000000
- convert to a string
- check the last 4 characters:
'000x' becomes '0000'
'999x' becomes '999999' (this is rounded automatically).
ROUNDMAGIC(VALUE/3*3)
SECURE_RAND
SECURE_RAND(int): bytes
Generates a number of cryptographically secure random numbers.
Example:CALL SECURE_RAND(16)
SIGN
SIGN({int | long | decimal | double}): int
Returns -1 if the value is smaller 0, 0 if zero, and otherwise 1.
Example:SIGN(VALUE)
ENCRYPT
ENCRYPT(algorithmString, keyBytes, dataBytes): bytes
Encrypts data using a key. Supported algorithms are XTEA and AES.
The block size is 16 bytes.
CALL ENCRYPT('AES', '00', STRINGTOUTF8('Test'))
DECRYPT
DECRYPT(algorithmString, keyBytes, dataBytes): bytes
Decrypts data using a key. Supported algorithms are XTEA and AES.
The block size is 16 bytes.
CALL TRIM(CHAR(0) FROM UTF8TOSTRING(DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116')))
HASH
HASH(algorithmString, dataBytes, iterationInt): bytes
Calculate the hash value using an algorithm, and repeat this process for a number of iterations.
Currently, the only algorithm supported is SHA256.
CALL HASH('SHA256', STRINGTOUTF8('Password'), 1000)
TRUNCATE
TRUNCATE(double, digitsInt): double
Truncates to a number of digits (to the next value closer to 0).
Example:TRUNCATE(VALUE, 2)
COMPRESS
COMPRESS(dataBytes [, algorithmString]): bytes
Compresses the data using the specified compression algorithm.
Supported algorithms are:
LZF (fast but lower compression; default),
DEFLATE (higher compression).
Compression does not always reduce size.
Very small objects and objects with little redundancy may get larger.
COMPRESS(STRINGTOUTF8('Test'))
EXPAND
EXPAND(bytes): bytes
Expands data that was compressed using the COMPRESS function.
Example:UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test'))))
ZERO
ZERO(): int
Returns the value 0. This function can be used even if numeric literals are disabled.
Example:ZERO()
ASCII
ASCII(string): int
Returns the ASCII value of the first character in the string.
Example:ASCII('Hi')
BIT_LENGTH
BIT_LENGTH(string): int
Returns the number of bits in a string.
For BLOB, CLOB, BYTES and JAVA_OBJECT, the precision is used.
Each character needs 16 bits.
BIT_LENGTH(NAME)
LENGTH
{LENGTH | CHAR_LENGTH | CHARACTER_LENGTH}(string): int
Returns the number of characters in a string.
For BLOB, CLOB, BYTES and JAVA_OBJECT, the precision is used.
LENGTH(NAME)
OCTET_LENGTH
OCTET_LENGTH(string): int
Returns the number of bytes in a string.
For BLOB, CLOB, BYTES and JAVA_OBJECT, the precision is used.
Each character needs 2 bytes.
OCTET_LENGTH(NAME)
CHAR
{CHAR | CHR}(int): string
Returns the character that represents the ASCII value.
Example:CHAR(65)
CONCAT
CONCAT(string, string [,...]): string
Combines strings.
Example:CONCAT(NAME, '!')
DIFFERENCE
DIFFERENCE(string, string): int
Returns the difference between the sounds of two strings.
Example:DIFFERENCE(T1.NAME, T2.NAME)
HEXTORAW
HEXTORAW(string): string
Converts a hex representation of a string to a string.
4 hex characters per string character are used.
HEXTORAW(DATA)
RAWTOHEX
RAWTOHEX(string): string
Converts a string to the hex representation.
4 hex characters per string character are used.
RAWTOHEX(DATA)
INSTR
INSTR(string, searchString, [, startInt]): int
Returns the location of a search string in a string (s).
If a start position is used, the characters before it are ignored.
If position is negative, the rightmost location is returned.
0 is returned if the search string is not found.
INSTR(EMAIL,'@')
INSERT Function
INSERT(originalString, startInt, lengthInt, addString): string
Inserts a additional string into the original string at a specified start position.
The length specifies the number of characters that are removed at the start position
in the original string.
INSERT(NAME, 1, 1, ' ')
LOWER
{LOWER | LCASE}(string): string
Converts a string to lowercase.
Example:LOWER(NAME)
UPPER
{UPPER | UCASE}(string): string
Converts a string to uppercase.
Example:UPPER(NAME)
LEFT
LEFT(string, int): string
Returns the leftmost number of characters.
Example:LEFT(NAME, 3)
RIGHT
RIGHT(string, int): string
Returns the rightmost number of characters.
Example:RIGHT(NAME, 3)
LOCATE
LOCATE(searchString, string [, startInt]): int
Returns the location of a search string in a string (s).
If a start position is used, the characters before it are ignored.
If position is negative, the rightmost location is returned.
0 is returned if the search string is not found.
LOCATE('.', NAME)
POSITION
POSITION(searchString, string): int
Returns the location of a search string in a string (s).
See also LOCATE.
POSITION('.', NAME)
LPAD
LPAD(string, int[, paddingString]): string
Left pad the string to the specified length.
If the length is shorter than the string, it will be truncated at the end.
If the padding string is not set, spaces will be used.
LPAD(AMOUNT, 10, '*')
RPAD
RPAD(string, int[, paddingString]): string
Right pad the string to the specified length.
If the length is shorter than the string, it will be truncated.
If the padding string is not set, spaces will be used.
RPAD(TEXT, 10, '-')
LTRIM
LTRIM(string): string
Removes all leading spaces from a string.
Example:LTRIM(NAME)
RTRIM
RTRIM(string): string
Removes all trailing spaces from a string.
Example:RTRIM(NAME)
TRIM
TRIM([{LEADING | TRAILING | BOTH} [string] FROM] string): string
Removes all leading spaces, trailing spaces, or spaces at both ends, from a string.
It is possible to remove other characters as well.
TRIM(BOTH '_' FROM NAME)
REGEXP_REPLACE
REGEXP_REPLACE(inputString, regexString, replacementString): string
Replaces each substring that matches a regular expression.
For details, see the Java String.replaceAll() method.
REGEXP_REPLACE('Hello World', ' +', ' ')
REPEAT
REPEAT(string, int): string
Returns a string repeated some number of times.
Example:REPEAT(NAME || ' ', 10)
REPLACE
REPLACE(string, searchString [, replacementString]): string
Replaces all occurrences of a search string in a text with another string.
If no replacement is specified, the search string is just removed from the original string.
REPLACE(NAME, ' ')
SOUNDEX
SOUNDEX(string): string
Returns a four character code representing the sound of a string.
See also http://www.archives.gov/genealogy/soundex.html .
SOUNDEX(NAME)
SPACE
SPACE(int): string
Returns a string consisting of a number of spaces.
Example:SPACE(80)
STRINGDECODE
STRINGDECODE(string): string
Converts a encoded string using the Java string literal encoding format.
Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>.
CALL STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))
STRINGENCODE
STRINGENCODE(string): string
Encodes special characters in a string using the Java string literal encoding format.
Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>.
CALL STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))
STRINGTOUTF8
STRINGTOUTF8(string): bytes
Encodes a string to a byte array using the UTF8 encoding format.
Example:CALL UTF8TOSTRING(STRINGTOUTF8('This is a test'))
SUBSTRING
{SUBSTRING | SUBSTR}(string, startInt [, lengthInt]): string
Returns a substring of a string starting at a position.
The length is optional.
Also supported is: SUBSTRING(string FROM start [FOR length]).
SUBSTR(NAME, 1)
UTF8TOSTRING
UTF8TOSTRING(bytes): string
Decodes a byte array in the UTF8 format to a string.
Example:CALL UTF8TOSTRING(STRINGTOUTF8('This is a test'))
XMLATTR
XMLATTR(nameString, valueString): string
Creates an XML attribute element of the form name="value".
The value is encoded as XML text.
CALL XMLNODE('a', XMLATTR('href', 'http://h2database.com'))
XMLNODE
XMLNODE(elementString [, attributesString [, contentString]]): string
Create an XML node element.
Example:CALL XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2')
XMLCOMMENT
XMLCOMMENT(commentString): string
Creates an XML comment. Two dashes (--) are converted to - -.
Example:CALL XMLCOMMENT('Test')
XMLCDATA
XMLCDATA(valueString): string
Creates an XML CDATA element. If the value contains ']]>', an XML text element is created instead.
Example:CALL XMLCDATA('data')
XMLSTARTDOC
XMLSTARTDOC(): string
The string '<?xml version="1.0"?>' is returned.
Example:CALL XMLSTARTDOC()
XMLTEXT
XMLTEXT(valueString): string
Creates an XML text element.
Example:CALL XMLTEXT('test')
ARRAY_GET
ARRAY_GET(arrayExpression, indexExpression): value
Returns one element of an array.
Example:CALL ARRAY_GET(('Hello', 'World'), 2)
ARRAY_LENGTH
ARRAY_GET(arrayExpression): int
Returns the length of an array.
Example:CALL ARRAY_LENGTH(('Hello', 'World'))
AUTOCOMMIT
AUTOCOMMIT(): boolean
Returns true if auto commit is switched on for this session.
Example:AUTOCOMMIT()
CANCEL_SESSION
CANCEL_SESSION(sessionInt): boolean
Cancels the currently executing statement of another session.
The method only works if the multithreaded kernel is enabled (see SET MULTI_THREADED).
Returns true if the statement was canceled, false if the session is closed
or no statement is currently executing.
Admin rights are required to execute this command.
CANCEL_SESSION(3)
CASEWHEN Function
CASEWHEN(boolean, aValue, bValue): value
Returns 'a' if the boolean expression is true, otherwise 'b'.
Example:CASEWHEN(ID=1, 'A', 'B')
CAST
CAST(value AS dataType): value
Converts a value to another data type.
Example:CAST(NAME AS INT)
COALESCE
COALESCE(aValue, bValue [,...]): value
Returns the first value that is not null.
Example:COALESCE(A, B, C)
CONVERT
CONVERT(value, dataType): value
Converts a value to another data type.
Example:CONVERT(NAME, INT)
CURRVAL
CURRVAL([schemaName, ] sequenceString): long
Returns the current (last) value of the sequence.
If the schema name is not set, the current schema is used.
If the schema name is not set, the sequence name is converted to uppercase (for compatibility).
CURRVAL('TEST_SEQ')
CSVREAD
CSVREAD(fileNameString [, columnNamesString [, charsetString [, fieldSeparatorString [, fieldDelimiterString [, escapeCharacterString [, nullString]]]]]]): resultSet
Returns the result set of reading the CSV (comma separated values) file.
For each parameter, NULL means the default value should be used.
If the column names are specified (a list of column names separated with the fieldSeparator),
those are used they are read from the file, otherwise (or if they are set to NULL) the first line
of the file is interpreted as the column names.
The default charset is the default value for this system, and the default field separator is a comma.
Missing unquoted values as well as data that matches the null string is parsed as NULL.
This function can be used like a table: SELECT * FROM CSVREAD(...).
Instead of a file, an URL may be used, for example jar:file:///c:/temp/example.zip!/org/example/nested.zip.
Admin rights are required to execute this command.
CALL CSVREAD('test.csv');
-- Read a file containing the columns ID, NAME with UTF-8 encoding and the pipe (|) as field separator
CALL CSVREAD('test2.csv', 'ID|NAME', 'UTF-8', '|');
-- Read a semicolon-separated file
SELECT * FROM CSVREAD('data/test.csv', NULL, NULL, ';');
CSVWRITE
CSVWRITE(fileNameString, queryString [, charsetString [, fieldSeparatorString [, fieldDelimiterString [, escapeCharacterString [, nullString [, lineSeparatorString]]]]]]): int
Writes a CSV (comma separated values).
The file is overwritten if it exists.
For each parameter, NULL means the default value should be used.
The default charset is the default value for this system, and the default field separator is a comma.
The null string is used when writing NULL (by default nothing is written when NULL appears).
The default line separator is the default value for this system ('line.separator' system property).
The returned value is the number or rows written.
Admin rights are required to execute this command.
CALL CSVWRITE('test.csv', 'SELECT * FROM TEST');
-- Write a file with UTF-8 encoding and the pipe (|) as field separator
CALL CSVWRITE('test2.csv', 'SELECT * FROM TEST', 'UTF-8', '|');
DATABASE
DATABASE(): string
Returns the name of the database.
Example:CALL DATABASE();
DATABASE_PATH
DATABASE_PATH(): string
Returns the directory of the database files and the database name, if it is file based.
Returns NULL otherwise.
CALL DATABASE_PATH();
FILE_READ
FILE_READ(fileNameString [,encodingString]): value
Returns the contents of a file. If only one parameter is supplied,
the data are returned as a BLOB. If two parameters are used,
the data is returned as a CLOB (text). The second parameter
is the character set to use, NULL meaning the default character set
for this system. File names and URLs are supported.
Admin rights are required to execute this command.
SELECT LENGTH(FILE_READ('~/.h2.server.properties')) LEN;
SELECT FILE_READ('http://localhost:8182/stylesheet.css', NULL) CSS;
GREATEST
GREATEST(aValue, bValue [,...]): value
Returns the largest value that is not NULL, or NULL if all values are NULL.
Example:CALL GREATEST(1, 2, 3);
IDENTITY
IDENTITY(): long
Returns the last inserted identity value for this session.
Example:CALL IDENTITY();
IFNULL
IFNULL(aValue, bValue): value
Returns the value of 'a' if it is not null, otherwise 'b'.
Example:CALL IFNULL(NULL, '');
LEAST
LEAST(aValue, bValue [,...]): value
Returns the smallest value that is not NULL, or NULL if all values are NULL.
Example:CALL LEAST(1, 2, 3);
LOCK_MODE
LOCK_MODE(): int
Returns the current lock mode. See SET LOCK_MODE.
Example:CALL LOCK_MODE();
LOCK_TIMEOUT
LOCK_TIMEOUT(): int
Returns the lock timeout of the current session (in milliseconds).
Example:LOCK_TIMEOUT()
LINK_SCHEMA
LINK_SCHEMA(targetSchemaString, driverString, urlString, userString, passwordString, sourceSchemaString): resultSet
Creates table links for all tables in a schema.
If tables with the same name already exist, they are dropped first.
The target schema is created automatically if it does not yet exist.
The driver name may be empty if the driver is already loaded.
The list of tables linked is returned.
Admin rights are required to execute this command.
CALL LINK_SCHEMA('TEST2', '', 'jdbc:h2:test2', 'sa', 'sa', 'PUBLIC');
MEMORY_FREE
MEMORY_FREE(): int
Returns the free memory in KB (where 1024 bytes is a KB).
The garbage is run before returning the value.
Admin rights are required to execute this command.
MEMORY_FREE()
MEMORY_USED
MEMORY_USED(): int
Returns the used memory in KB (where 1024 bytes is a KB).
The garbage is run before returning the value.
Admin rights are required to execute this command.
MEMORY_USED()
NEXTVAL
NEXTVAL([schemaName, ] sequenceString): long
Returns the next value of the sequence.
If the schema name is not set, the current schema is used.
If the schema name is not set, the sequence name is converted to uppercase (for compatibility).
NEXTVAL('TEST_SEQ')
NULLIF
NULLIF(aValue, bValue): value
Returns NULL if 'a' is equals to 'b', otherwise 'a'.
Example:NULLIF(A, B)
READONLY
READONLY(): boolean
Returns true if the database is read-only.
Example:READONLY()
ROWNUM
ROWNUM(): int
Returns the number of the current row. This function is supported for SELECT statements,
as well as for DELETE and UPDATE. The first row has the row number 1, and is calculated
before ordering and grouping the result set.
SELECT ROWNUM(), * FROM TEST
SCHEMA
SCHEMA(): string
Returns the name of the default schema for this session.
Example:CALL SCHEMA()
SESSION_ID
SESSION_ID(): int
Returns the unique session id number for the current database connection.
This id stays the same while the connection is open.
The database engine may re-use a session id after the connection is closed.
CALL SESSION_ID()
SET
SET(@variableName, value): value
Updates a variable with the given value. The new value is returned.
When used in a query, the value is updated in the order the rows are read.
SELECT X, SET(@I, IFNULL(@I, 0)+X) RUNNING_TOTAL FROM SYSTEM_RANGE(1, 10)
TABLE
TABLE|TABLE_DISTINCT( { name dataType = expression } [,..]): result set
Returns the result set. TABLE_DISTINCT removes duplicate rows.
Example:SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World'))
USER
{USER | CURRENT_USER}(): string
Returns the name of the current user of this session.
Example:CURRENT_USER()
CURRENT_DATE
{CURRENT_DATE[()] | CURDATE() | SYSDATE | TODAY}: date
Returns the current date.
Example:CURRENT_DATE()
CURRENT_TIME
{CURRENT_TIME[()] | CURTIME()}: time
Returns the current time.
Example:CURRENT_TIME()
CURRENT_TIMESTAMP
{CURRENT_TIMESTAMP[([int])] | NOW([int])}: timestamp
Returns the current timestamp.
The precision parameter for nanoseconds precision is optional.
CURRENT_TIMESTAMP()
DATEADD
DATEADD(unitString, addInt, timestamp): timestamp
Adds units to a timestamp. The string indicates the unit. Use negative values to subtract units.
The same units as in the EXTRACT function are supported.
DATEADD('MONTH', 1, DATE '2001-01-31')
DATEDIFF
DATEDIFF(unitString, aTimestamp, bTimestamp): long
Returns the difference between two timestamps. The string indicates the unit.
The same units as in the EXTRACT function are supported.
DATEDIFF('YEAR', T1.CREATED, T2.CREATED)
DAYNAME
DAYNAME(date): string
Returns the name of the day (in English).
Example:DAYNAME(CREATED)
DAY_OF_MONTH
DAY_OF_MONTH(date): int
Returns the day of the month (1-31).
Example:DAY_OF_MONTH(CREATED)
DAY_OF_WEEK
DAY_OF_WEEK(date): int
Returns the day of the week (1 means Sunday).
Example:DAY_OF_WEEK(CREATED)
DAY_OF_YEAR
DAY_OF_YEAR(date): int
Returns the day of the year (1-366).
Example:DAY_OF_YEAR(CREATED)
EXTRACT
EXTRACT( {YEAR | YY | MONTH | MM | DAY | DD | DAY_OF_YEAR | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS} FROM timestamp): int
Returns a specific value from a timestamps.
Example:EXTRACT(SECOND FROM CURRENT_TIMESTAMP)
FORMATDATETIME
FORMATDATETIME(timestamp, formatString [, localeString [, timeZoneString]]): string
Formats a date, time or timestamp as a string.
The most important format characters are: y year, M month, d day, H hour, m minute, s second
For details of the format, see java.text.SimpleDateFormat.
CALL FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
HOUR
HOUR(timestamp): int
Returns the hour (0-23) from a timestamp.
Example:HOUR(CREATED)
MINUTE
MINUTE(timestamp): int
Returns the minute (0-59) from a timestamp.
Example:MINUTE(CREATED)
MONTH
MONTH(timestamp): int
Returns the month (1-12) from a timestamp.
Example:MONTH(CREATED)
MONTHNAME
MONTHNAME(date): string
Returns the name of the month (in English).
Example:MONTHNAME(CREATED)
PARSEDATETIME
PARSEDATETIME(string, formatString [, localeString [, timeZoneString]]): string
Parses a string and returns a timestamp.
The most important format characters are: y year, M month, d day, H hour, m minute, s second
For details of the format, see java.text.SimpleDateFormat.
CALL PARSEDATETIME('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
QUARTER
QUARTER(timestamp): int
Returns the quarter (1-4) from a timestamp.
Example:QUARTER(CREATED)
SECOND
SECOND(timestamp): int
Returns the second (0-59) from a timestamp.
Example:SECOND(CREATED)
WEEK
WEEK(timestamp): int
Returns the week (1-53) from a timestamp.
This method uses the current system locale.
WEEK(CREATED)
YEAR
YEAR(timestamp): int
Returns the year from a timestamp.
Example:YEAR(CREATED)