Aggregate Functions

AVG COUNT APPROX_COUNT_DISTINCT MAX MIN SUM

PERCENTILE_CONT PERCENTILE_DISC PERCENT_RANK FIRST_VALUE LAST_VALUE FIRST_VALUES

LAST_VALUES NTH_VALUE STDDEV_POP STDDEV_SAMP

String Functions

SUBSTR INSTR TRIM LTRIM RTRIM

LPAD LENGTH REGEXP_SUBSTR REGEXP_REPLACE REGEXP_SPLIT

UPPER LOWER REVERSE TO_CHAR COLLATION_KEY

Time and Date Functions

TO_DATE CURRENT_DATE TO_TIME TO_TIMESTAMP CURRENT_TIME CONVERT_TZ

TIMEZONE_OFFSET NOW YEAR MONTH WEEK DAYOFYEAR

DAYOFMONTH DAYOFWEEK HOUR MINUTE SECOND

Numeric Functions

ROUND CEIL

FLOOR TRUNC

TO_NUMBER RAND

Array Functions

ARRAY_ELEM ARRAY_LENGTH ARRAY_APPEND

ARRAY_PREPEND ARRAY_CAT ARRAY_FILL

ARRAY_TO_STRING ANY ALL

Math Functions

SIGN ABS SQRT

CBRT EXP POWER

LN LOG

Other Functions

MD5 INVERT ENCODE DECODE

COALESCE GET_BIT GET_BYTE OCTET_LENGTH

SET_BIT SET_BYTE

AVG

AVG ( { numericTerm } )

The average (mean) value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Example:

AVG(X)

COUNT

COUNT( [ DISTINCT ] { * | { term } } )

The count of all row, or of the non-null values. This method returns a long. When DISTINCT is used, it counts only distinct values. If no rows are selected, the result is 0. Aggregates are only allowed in select statements.

Example:

COUNT(*)

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT( { * | { term } } )

The approximate distinct count of all row, or of the non-null values. The relative error of approximation by default is less than 0.00405 This method returns a long. If no rows are selected, the result is 0. Aggregates are only allowed in select statements.

Example:

APPROX_COUNT_DISTINCT(*)

MAX

MAX(term)

The highest value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Example:

MAX(NAME)

MIN

MIN(term)

The lowest value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Example:

MIN(NAME)

SUM

SUM( { numericTerm } )

The sum of all values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Example:

SUM(X)

PERCENTILE_CONT

PERCENTILE_CONT( { numeric } ) WITHIN GROUP (ORDER BY { numericTerm } { ASC | DESC } )

The nth percentile of values in the column. The percentile value can be between 0 and 1 inclusive. Aggregates are only allowed in select statements. The returned value is of decimal data type.

Example:

PERCENTILE_CONT( 0.9 ) WITHIN GROUP (ORDER BY X ASC)

PERCENTILE_DISC

PERCENTILE_DIST( { numeric } ) WITHIN GROUP (ORDER BY { numericTerm } { ASC | DESC } )

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.

Example:

PERCENTILE_DISC( 0.9 ) WITHIN GROUP (ORDER BY X DESC)

PERCENT_RANK

PERCENT_RANK( { numeric } ) WITHIN GROUP (ORDER BY { numericTerm } { ASC | DESC } )

The percentile rank for a hypothetical value, if inserted into the column. Aggregates are only allowed in select statements. The returned value is of decimal data type.

Example:

PERCENT_RANK( 100 ) WITHIN GROUP (ORDER BY X ASC)

FIRST_VALUE

FIRST_VALUE( { expression } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )

The first value in each distinct group ordered according to the ORDER BY specification.

Example:

FIRST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)

LAST_VALUE

LAST_VALUE( { expression } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )

The last value in each distinct group ordered according to the ORDER BY specification.

Example:

LAST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)

FIRST_VALUES

FIRST_VALUES( { expression , numeric } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )

Returns an array of at most the given numeric size of the first values in each distinct group ordered according to the ORDER BY specification.

Example:

FIRST_VALUES( name, 3 ) WITHIN GROUP (ORDER BY salary DESC)

LAST_VALUES

LAST_VALUES( { expression , numeric } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )

Returns an array of at most the given numeric size of the last values in each distinct group ordered according to the ORDER BY specification.

Example:

LAST_VALUES( name, 3 ) WITHIN GROUP (ORDER BY salary DESC)

NTH_VALUE

NTH_VALUE( { expression, nthNumeric } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )

The nth value in each distinct group ordered according to the ORDER BY specification.

Example:

NTH_VALUE( name, 2 ) WITHIN GROUP (ORDER BY salary DESC)

STDDEV_POP

STDDEV_POP( { numericTerm } )

The population standard deviation of all values. Aggregates are only allowed in select statements. The returned value is of decimal data type.

Example:

STDDEV_POP( X )

STDDEV_SAMP

STDDEV_SAMP( { numericTerm } )

The sample standard deviation of all values. Aggregates are only allowed in select statements. The returned value is of decimal data type.

Example:

STDDEV_SAMP( X )

ARRAY_ELEM

ARRAY_ELEM( arrayTerm, numericTerm )

Alternative to using array subscript notation to access an array element. Returns the element in the array at the given position. The position is one-based.

Example:

ARRAY_ELEM(my_array_col, 5)
ARRAY_ELEM(ARRAY[1,2,3], 1)

ARRAY_LENGTH

ARRAY_LENGTH( arrayTerm )

Returns the current length of the array.

Example:

ARRAY_LENGTH(my_array_col)
ARRAY_LENGTH(ARRAY[1,2,3])

ARRAY_APPEND

ARRAY_APPEND( arrayTerm, elementTerm )

Appends the given element to the end of the array.

Example:

ARRAY_APPEND(my_array_col, my_element_col)
ARRAY_APPEND(ARRAY[1,2,3], 4) evaluates to ARRAY[1,2,3,4]

ARRAY_PREPEND

ARRAY_PREPEND(elementTerm, arrayTerm)

Appends the given element to the beginning of the array.

Example:

ARRAY_PREPEND(my_element_col, my_array_col)
ARRAY_PREPEND(0, ARRAY[1,2,3]) evaluates to ARRAY[0,1,2,3]

ARRAY_CAT

ARRAY_CAT(arrayTerm, arrayTerm)

Concatenates the input arrays and returns the result.

Example:

ARRAY_CAT(my_array_col1, my_array_col2)
ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]) evaluates to ARRAY[1,2,3,4]

ARRAY_FILL

ARRAY_FILL(arrayTerm, lengthNumeric)

Returns an array initialized with supplied value and length.

Example:

ARRAY_FILL(my_element_col, my_length_col)
ARRAY_FILL(1, 3) evaluates to ARRAY[1,1,1]

ARRAY_TO_STRING

ARRAY_TO_STRING(arrayTerm, delimiterString [, nullString])

Concatenates array elements using supplied delimiter and optional null string and returns the resulting string. If the nullString parameter is omitted or NULL, any null elements in the array are simply skipped and not represented in the output string.

Example:

ARRAY_TO_STRING(my_array_col, my_delimiter_col, my_null_string_col)
ARRAY_TO_STRING(ARRAY['a','b','c'], ',') evaluates to 'a,b,c'
ARRAY_TO_STRING(ARRAY['a','b',null,'c'], ',') evaluates to 'a,b,c'
ARRAY_TO_STRING(ARRAY['a','b',null,'c'], ',', 'NULL') evaluates to 'a,b,NULL,c'

ANY

ANY( arrayTerm )

Used on the right-hand side of a comparison expression to test that any array element satisfies the comparison expression against the left-hand side.

Example:

1 = ANY(my_array)
10 > ANY(my_array)

ALL

ALL( arrayTerm )

Used on the right-hand side of a comparison expression to test that all array elements satisfy the comparison expression against the left-hand side. of the array.

Example:

1 = ALL(my_array)
10 > ALL(my_array)

MD5

MD5( term )

Computes the MD5 hash of the argument, returning the result as a BINARY(16).

Example:

MD5(my_column)

INVERT

INVERT( term )

Inverts the bits of the argument. The return type will be the same as the argument.

Example:

INVERT(my_column)

ENCODE

ENCODE( expression, 'BASE62' )

Encodes the expression according to the encoding format provided and returns the resulting string. For 'BASE62', converts the given base 10 number to a base 62 number and returns a string representing the number.

Example:

ENCODE(myNumber, 'BASE62')

DECODE

DECODE( expression, 'HEX' )

Decodes the expression according to the encoding format provided and returns the resulting value as a VARBINARY. For 'HEX', converts the hex string expression to its binary representation, providing a mechanism for inputting binary data through the console.

Example:

DECODE('000000008512af277ffffff8', 'HEX')

COALESCE

COALESCE( firstTerm, secondTerm )

Returns the value of the first argument if not null and the second argument otherwise. Useful to guarantee that a column in an UPSERT SELECT command will evaluate to a non null value.

Example:

COALESCE(last_update_date, CURRENT_DATE())

GET_BIT

GET_BIT( binaryValue, offsetInt )

Retrieves the bit at the given index in the given binary value.

Example:

GET_BIT(CAST('FFFF' as BINARY), 1)

GET_BYTE

GET_BYTE( binaryValue, offsetInt )

Retrieves the byte at the given index in the given binary value.

Example:

GET_BYTE(CAST('FFFF' as BINARY), 1)

OCTET_LENGTH

OCTET_LENGTH( binaryValue )

Returns the number of bytes in a binary value.

Example:

OCTET_LENGTH(NAME)

SET_BIT

SET_BIT( binaryValue, offsetInt, newValue )

Replaces the bit at the given index in the binary value with the provided newValue.

Example:

SET_BIT(CAST('FFFF' as BINARY), 1, 61)

SET_BYTE

SET_BYTE( binaryValue, offsetInt, newValue )

Replaces the byte at the given index in the binary value with the provided newValue.

Example:

SET_BYTE(CAST('FFFF' as BINARY), 1, 61)

SIGN

SIGN(numericTerm)

Returns the signum function of the given numeric expression as an INTEGER. The return value is -1 if the given numeric expression is negative; 0 if the given numeric expression is zero; and 1 if the given numeric expression is positive.

Example:

SIGN(number)
SIGN(1.1)
SIGN(-1)

ABS

ABS(numericTerm)

Returns the absolute value of the given numeric expression maintaining the same type.

Example:

ABS(number)
ABS(1.1)
ABS(-1)

SQRT

SQRT(numericTerm)

Returns the correctly rounded square root of the given non-negative numeric expression as a DOUBLE.

Example:

SQRT(number)
SQRT(1.1)

CBRT

CBRT(numericTerm)

Returns the cube root of the given numeric expression as a DOUBLE.

Example:

CBRT(number)
CBRT(1.1)
CBRT(-1)

EXP

EXP(numericTerm)

Returns Euler's number e raised to the power of the given numeric value as a DOUBLE.

Example:

EXP(number)
EXP(1.1)
EXP(-1)

POWER

POWER(numericTerm, numericTerm)

Returns the value of the first argument raised to the power of the second argument as a DOUBLE.

Example:

POWER(number, number)
POWER(3, 2)
POWER(2, 3)

LN

LN(numericTerm)

Returns the natural logarithm (base e) of the given positive expression as a DOUBLE.

Example:

LN(number)
LN(3)
LN(2)

LOG

LOG(numericTerm[, numericTerm])

Returns the logarithm of the first argument computed at the base of the second argument as a DOUBLE. If omitted, a base of 10 will be used for the second argument.

Example:

LOG(3, 2)
LOG(2, 3)
LOG(2)

ROUND

ROUND({numericTerm | dateTimeTerm} [, scaleNumber | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, multiplierNumber]])

Rounds the numeric or timestamp expression to the nearest scale or time unit specified. If the expression is a numeric type, then the second argument is the scale to be used for rounding off the number, defaulting to zero. If the expression is a date/time type, then the second argument may be one of the time units listed to determine the remaining precision of the date/time. A default of MILLISECONDS is used if not present. The multiplier is only applicable for a date/time type and is used to round to a multiple of a time unit (i.e. 10 minute) and defaults to 1 if not specified. This method returns the same type as its first argument.

Example:

ROUND(number)
ROUND(number, 2)
ROUND(timestamp)
ROUND(time, 'HOUR')
ROUND(date, 'MINUTE', 30)

CEIL

CEIL({numericTerm | dateTimeTerm} [, scaleNumber | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, multiplierInt]])

Same as ROUND, except it rounds any fractional value up to the next even multiple.

Example:

CEIL(number, 3)
CEIL(2.34)
CEIL(timestamp, 'SECOND', 30)
CEIL(date, 'DAY', 7)

FLOOR

FLOOR({numericTerm | dateTimeTerm} [, scaleNumber | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, multiplierInt]])

Same as ROUND, except it rounds any fractional value down to the previous even multiple.

Example:

FLOOR(timestamp)
FLOOR(date, 'DAY', 7)

TRUNC

TRUNC({numericTerm | dateTimeTerm} [, scaleNumber | {'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND'} [, multiplierInt]])

Same as FLOOR

Example:

TRUNC(timestamp, 'SECOND', 30)
TRUNC(date, 'DAY', 7)

TO_NUMBER

TO_NUMBER( { stringTerm | dateTimeTerm } [, formatString] )

Formats a string or date/time type as a number, optionally accepting a format string. For details on the format, see java.text.DecimalFormat. For date, time, and timeStamp terms, the result is the time in milliseconds since the epoch. This method returns a decimal number.

Example:

TO_NUMBER('$123.33', '\u00A4###.##')

RAND

RAND( [seedNumber] )

Function that produces a random, uniformly distributed double value between 0.0 (inclusive) and 1.0 (exclusive). If a seed is provided, then the the returned value is identical across each invocation for the same row. If a seed is not provided, then the returned value is different for each invocation. The seed must be a constant.

Example:

RAND()
RAND(5)

SUBSTR

SUBSTR( stringTerm, startInt [, lengthInt ] )

Returns a substring of a string starting at the one-based position. If zero is used, the position is zero-based. If the start index is negative, then the start index is relative to the end of the string. The length is optional and if not supplied, the rest of the string will be returned.

Example:

SUBSTR('[Hello]', 2, 5)
SUBSTR('Hello World', -5)

INSTR

INSTR( stringTerm, stringTerm )

Returns the one-based position of the initial occurrence of the second argument in the first argument. If the second argument is not contained in the first argument, then zero is returned.

Example:

INSTR('Hello World', 'World')
INSTR('Simon says', 'mon')
INSTR('Peace on earth', 'war')

TRIM

TRIM( stringTerm )

Removes leading and trailing spaces from the input string.

Example:

TRIM('  Hello  ')

LTRIM

LTRIM( stringTerm )

Removes leading spaces from the input string.

Example:

LTRIM('  Hello')

RTRIM

RTRIM( stringTerm )

Removes trailing spaces from the input string.

Example:

RTRIM('Hello   ')

LPAD

LPAD( stringTerm, lengthNumeric, [padString] )

Pads the string expression with the specific pad character (space by default) up to the length argument.

Example:

LPAD('John',30)

LENGTH

LENGTH( stringTerm )

Returns the length of the string in characters.

Example:

LENGTH('Hello')

REGEXP_SUBSTR

REGEXP_SUBSTR( stringTerm, patternString [, startInt ] )

Returns a substring of a string by applying a regular expression start from the offset of a one-based position. Just like with SUBSTR, if the start index is negative, then it is relative to the end of the string. If not specified, the start index defaults to 1.

Example:

REGEXP_SUBSTR('na1-appsrv35-sj35', '[^-]+') evaluates to 'na1'

REGEXP_REPLACE

REGEXP_REPLACE( stringTerm, patternString [, replacementString ] )

Returns a string by applying a regular expression and replacing the matches with the replacement string. If the replacement string is not specified, it defaults to an empty string.

Example:

REGEXP_REPLACE('abc123ABC', '[0-9]+', '#') evaluates to 'abc#ABC'

REGEXP_SPLIT

REGEXP_SPLIT( stringTerm, patternTerm )

Splits a string into a VARCHAR ARRAY using a regular expression. If characters that have a special meaning in regular expressions are to be used as a regular delimiter in the pattern string, they must be escaped with backslashes.

Example:

REGEXP_SPLIT('ONE,TWO,THREE', ',') evaluates to ARRAY['ONE', 'TWO', 'THREE']
REGEXP_SPLIT('ONE!#TWO#,!THREE', '[,!#]+') evaluates to ARRAY['ONE', 'TWO', 'THREE']

UPPER

UPPER( stringTerm [, localeString ] )

Returns upper case string of the string argument. If localeString (available in Phoenix 4.14) is provided, it identifies the locale whose rules are used for the conversion. If localeString is not provided, the default locale is used. The localeString must be of the form returned by the Java 6 implementation of java.util.Locale.toString() e.g. 'zh_TW_STROKE' or 'en_US' or 'fr_FR'.

Example:

UPPER('Hello')
UPPER('Hello', 'tr_TR')

LOWER

LOWER( stringTerm [, localeString ] )

Returns lower case string of the string argument. If localeString (available in Phoenix 4.14) is provided, it identifies the locale whose rules are used for the conversion. If localeString is not provided, the default locale is used. The localeString must be of the form returned by the Java 6 implementation of java.util.Locale.toString() e.g. 'zh_TW_STROKE' or 'en_US' or 'fr_FR'.

Example:

LOWER('HELLO')
LOWER('HELLO', 'en_US')

REVERSE

REVERSE( stringTerm )

Returns reversed string of the string argument.

Example:

REVERSE('Hello')

TO_CHAR

TO_CHAR( { timestampTerm | numberTerm } [, formatString] )

Formats a date, time, timestamp, or number as a string. The default date format is yyyy-MM-dd HH:mm:ss and the default number format is #,##0.###. For details, see java.text.SimpleDateFormat for date/time values and java.text.DecimalFormat for numbers. This method returns a string.

Example:

TO_CHAR(myDate, '2001-02-03 04:05:06')
TO_CHAR(myDecimal, '#,##0.###')

COLLATION_KEY

COLLATION_KEY( stringTerm, localeString [, upperCaseBoolean [, strengthInt [, decompositionInt ] ] ] )

Calculates a collation key that can be used to sort strings in a natural-language-aware way. The localeString must be of the form returned by the Java 6 implementation of java.util.Locale.toString() e.g. 'zh_TW_STROKE' or 'en_US' or 'fr_FR'. The third, fourth and fifth arguments are optional and determine respectively whether to use a special upper-case collator, the strength value of the collator, and the decomposition value of the collator. (See java.text.Collator to learn about strength and decomposition).

Example:

SELECT NAME FROM EMPLOYEE ORDER BY COLLATION_KEY(NAME, 'zh_TW')

TO_DATE

TO_DATE( stringTerm [, formatString [, timeZoneIdString]] )

Parses a string and returns a date. Note that the returned date is internally represented as the number of milliseconds since the java epoch. The most important format characters are: y year, M month, d day, H hour, m minute, s second. The default format string is yyyy-MM-dd HH:mm:ss. For details of the format, see java.text.SimpleDateFormat. By default, GMT will be used as the time zone when parsing the date. However, a time zone id can also be supplied. This is a time zone id such as 'GMT+1'. If 'local' is provided as the time zone id, the local time zone will be used for parsing. The configuration setting phoenix.query.dateFormatTimeZone can also be set to a time zone id, which will cause the default of GMT to be overridden with the configured time zone id. Please see the Data Type reference guide about how Apache Phoenix presently defines the DATE datatype. Additionally, Phoenix supports the ANSI SQL date literal which acts similarly to the single-argument TO_DATE function.

Example:

TO_DATE('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z')
TO_DATE('1970-01-01', 'yyyy-MM-dd', 'GMT+1')
date '1970-01-01 12:30:00'

CURRENT_DATE

CURRENT_DATE()

Returns the current server-side date, bound at the start of the execution of a query based on the current time on the region server owning the metadata of the table being queried. Please see the Data Type reference guide about how Apache Phoenix presently defines the DATE datatype.

Example:

CURRENT_DATE()

TO_TIME

TO_TIME( string [, formatString [, timeZoneIdString]] )

Converts the given string into a TIME instance. When a date format is not provided it defaults to yyyy-MM-dd HH:mm:ss.SSS or whatever is defined by the configuration property phoenix.query.dateFormat. The configuration setting phoenix.query.dateFormatTimeZone can also be set to a time zone id, which will cause the default of GMT to be overridden with the configured time zone id. Additionally, Phoenix supports the ANSI SQL time literal which acts similarly to the single-argument TO_TIME function.

Example:

TO_TIME('2005-10-01 14:03:22.559')
TO_TIME('1970-01-01', 'yyyy-MM-dd', 'GMT+1')
time '2005-10-01 14:03:22.559'

TO_TIMESTAMP

TO_TIMESTAMP( string [, formatString [, timeZoneIdString]] )

Converts the given string into a TIMESTAMP instance. When a date format is not provided it defaults to yyyy-MM-dd HH:mm:ss.SSS or whatever is defined by the configuration property phoenix.query.dateFormat. The configuration setting phoenix.query.dateFormatTimeZone can also be set to a time zone id, which will cause the default of GMT to be overridden with the configured time zone id. Additionally, Phoenix supports the ANSI SQL timestamp literal which acts similarly to the single-argument TO_TIMESTAMP function.

Example:

TO_TIMESTAMP('2005-10-01 14:03:22.559')
TO_TIMESTAMP('1970-01-01', 'yyyy-MM-dd', 'GMT+1')
timestamp '2005-10-01 14:03:22.559'

CURRENT_TIME

CURRENT_TIME()

Same as CURRENT_DATE(), except returns a value of type TIME. In either case, the underlying representation is the epoch time as a long value. Please see the Data Type reference guide about how Apache Phoenix presently defines the TIME datatype.

Example:

CURRENT_TIME()

CONVERT_TZ

CONVERT_TZ(dateTerm|timeTerm|timestampTerm, fromTimeZoneString, toTimeZoneString)

Converts date/time from one time zone to another returning the shifted date/time value.

Example:

CONVERT_TZ(myDate, 'UTC', 'Europe/Prague')

TIMEZONE_OFFSET

TIMEZONE_OFFSET(timeZoneString, dateTerm|timeTerm|timestampTerm)

Returns offset (shift in minutes) of a time zone at particular date/time in minutes.

Example:

TIMEZONE_OFFSET('Indian/Cocos', myDate)

NOW

NOW()

Returns the current date, bound at the start of the execution of a query based on the current time on the region server owning the metadata of the table being queried.

Example:

NOW()

YEAR

YEAR(dateTerm|timeTerm|timestampTerm)

Returns the year of the specified date.

Example:

YEAR(TO_DATE('2015-6-05'))

MONTH

MONTH(dateTerm|timeTerm|timestampTerm)

Returns the month of the specified date.

Example:

MONTH(TO_TIMESTAMP('2015-6-05'))

WEEK

WEEK(dateTerm|timeTerm|timestampTerm)

Returns the week of the specified date.

Example:

WEEK(TO_TIME('2010-6-15'))

DAYOFYEAR

DAYOFYEAR(dateTerm|timeTerm|timestampTerm)

Returns the day of the year of the specified date.

Example:

DAYOFYEAR(TO_DATE('2004-01-18 10:00:10'))

DAYOFMONTH

DAYOFMONTH(dateTerm|timeTerm|timestampTerm)

Returns the day of the month of the specified date.

Example:

DAYOFMONTH(TO_DATE('2004-01-18 10:00:10'))

DAYOFWEEK

DAYOFWEEK(dateTerm|timeTerm|timestampTerm)

Returns the day of the week of the specified date.

Example:

DAYOFWEEK(TO_DATE('2004-01-18 10:00:10'))

HOUR

HOUR(dateTerm|timeTerm|timestampTerm)

Returns the hour of the specified date.

Example:

HOUR(TO_TIMESTAMP('2015-6-05'))

MINUTE

MINUTE(dateTerm|timeTerm|timestampTerm)

Returns the minute of the specified date.

Example:

MINUTE(TO_TIME('2015-6-05'))

SECOND

SECOND(dateTerm|timeTerm|timestampTerm)

Returns the second of the specified date.

Example:

SECOND(TO_DATE('2015-6-05'))