--- title: "Built-in function call" url: "https://docs.yugabyte.com/stable/api/ycql/expr_fcall/" --- # Built-in function call Use a function call expression to apply the specified function to given arguments between parentheses and return the result of the computation. ## Synopsis Use a function call expression to apply the specified function to given arguments between parentheses and return the result of the computation. ## Syntax ```sql function_call ::= function_name '(' [ arguments ... ] ')' ``` ## Built-in Functions | Function | Return Type | Argument Type | Description | |-----------------------------------------------------------------------------------------------------------|------------------------------------------------|------------------------------------------------|-----------------------------------------------------------------------------------| | BlobAs<Type> | <Type> | ([`BLOB`](/stable/api/ycql/type_blob "BLOB")) | Converts a value from `BLOB` | | <Type>AsBlob | [`BLOB`](/stable/api/ycql/type_blob "BLOB") | (<Type>) | Converts a value to `BLOB` | | [DateOf](/stable/api/ycql/function_datetime/#dateof "DateOf") | [`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP") | ([`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID")) | Conversion | | [MaxTimeUuid](/stable/api/ycql/function_datetime/#maxtimeuuid-timestamp "MaxTimeUuid") | [`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime "TIMESTAMP")) | Returns the associated max time UUID | | [MinTimeUuid](/stable/api/ycql/function_datetime/#mintimeuuid-timestamp "MinTimeUuid") | [`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime "TIMESTAMP")) | Returns the associated min time UUID | | [CurrentDate](/stable/api/ycql/function_datetime/#currentdate-currenttime-and-currenttimestamp "CurrentDate") | [`DATE`](/stable/api/ycql/type_datetime/ "DATE") | () | Return the system current date | | [CurrentTime](/stable/api/ycql/function_datetime/#currentdate-currenttime-and-currenttimestamp "CurrentTime") | [`TIME`](/stable/api/ycql/type_datetime/ "TIME") | () | Return the system current time of day | | [CurrentTimestamp](/stable/api/ycql/function_datetime/#currentdate-currenttime-and-currenttimestamp "CurrentTimestamp") | [`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP") | () | Return the system current timestamp | | [Now](/stable/api/ycql/function_datetime/#now "Now") | [`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID") | () | Returns the UUID of the current timestamp | | [TTL](#ttl-function "TTL") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | (<AnyType>) | Get time-to-live of a column | | [ToDate](/stable/api/ycql/function_datetime/#todate "ToDate") | [`DATE`](/stable/api/ycql/type_datetime/ "DATE") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP")) | Conversion | | [ToDate](/stable/api/ycql/function_datetime/#todate "ToDate") | [`DATE`](/stable/api/ycql/type_datetime/ "DATE") | ([`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID")) | Conversion | | ToTime | [`TIME`](/stable/api/ycql/type_datetime/ "TIME") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP")) | Conversion | | ToTime | [`TIME`](/stable/api/ycql/type_datetime/ "TIME") | ([`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID") | Conversion | | [ToTimestamp](/stable/api/ycql/function_datetime/#totimestamp "ToTimestamp") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP")) | ([`DATE`](/stable/api/ycql/type_datetime/ "DATE")) | Conversion | | [ToTimestamp](/stable/api/ycql/function_datetime/#totimestamp "ToTimestamp") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP")) | (`TIMEUUID`) | Conversion | | [ToUnixTimestamp](/stable/api/ycql/function_datetime/#tounixtimestamp "ToUnixTimestamp") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | ([`DATE`](/stable/api/ycql/type_datetime/ "DATE")) | Conversion | | [ToUnixTimestamp](/stable/api/ycql/function_datetime/#tounixtimestamp "ToUnixTimestamp") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | ([`TIMESTAMP`](/stable/api/ycql/type_datetime/ "TIMESTAMP")) | Conversion | | [ToUnixTimestamp](/stable/api/ycql/function_datetime/#tounixtimestamp "ToUnixTimestamp") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | ([`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID")) | Conversion | | [UnixTimestampOf](/stable/api/ycql/function_datetime/#unixtimestampof "UnixTimestampOf") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | ([`TIMEUUID`](/stable/api/ycql/type_uuid "TIMEUUID")) | Conversion | | [UUID](/stable/api/ycql/function_datetime/#uuid "UUID") | [`UUID`](/stable/api/ycql/type_uuid "UUID") | () | Returns a version 4 UUID | | [WriteTime](#writetime-function "WriteTime") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | (<AnyType>) | Returns the timestamp when the column was written | | [partition\_hash](#partition-hash-function "partition_hash") | [`BIGINT`](/stable/api/ycql/type_int "BIGINT") | () | Computes the partition hash value (uint16) for the partition key columns of a row | ## Aggregate Functions | Function | Description | |----------|--------------------------------------------| | COUNT | Returns number of selected rows | | SUM | Returns sums of column values | | AVG | Returns the average of column values | | MIN | Returns the minimum value of column values | | MAX | Returns the maximum value of column values | ## Semantics - The argument data types must be convertible to the expected type for that argument that was specified by the function definition. - Function execution will return a value of the specified type by the function definition. - YugabyteDB allows function calls to be used any where that expression is allowed. ## CAST function CAST function converts the value returned from a table column to the specified data type. ### Syntax ```sql cast_call ::= CAST '(' column AS type ')' ``` The following table lists the column data types and the target data types. | Source column type | Target data type | |--------------------|-------------------------------------| | `BIGINT` | `SMALLINT`, `INT`, `TEXT` | | `BOOLEAN` | `TEXT` | | `DATE` | `TEXT`, `TIMESTAMP` | | `DOUBLE` | `BIGINT`, `INT`, `SMALLINT`, `TEXT` | | `FLOAT` | `BIGINT`, `INT`, `SMALLINT`, `TEXT` | | `INT` | `BIGINT`, `SMALLINT`, `TEXT` | | `SMALLINT` | `BIGINT`, `INT`, `TEXT` | | `TIME` | `TEXT` | | `TIMESTAMP` | `DATE`, `TEXT` | | `TIMEUUID` | `DATE`, `TIMESTAMP` | ### Example ```sql ycqlsh:example> CREATE TABLE test_cast (k INT PRIMARY KEY, ts TIMESTAMP); ``` ```sql ycqlsh:example> INSERT INTO test_cast (k, ts) VALUES (1, '2018-10-09 12:00:00'); ``` ```sql ycqlsh:example> SELECT CAST(ts AS DATE) FROM test_cast; ``` ```output cast(ts as date) ------------------ 2018-10-09 ``` ## partition\_hash function `partition_hash` is a function that takes as arguments the partition key columns of the primary key of a row and returns a `uint16` hash value representing the hash value for the row used for partitioning the table. The hash values used for partitioning fall in the `0-65535` (uint16) range. Tables are partitioned into tablets, with each tablet being responsible for a range of partition values. The `partition_hash` of the row is used to decide which tablet the row will reside in. `partition_hash` can be beneficial for querying a subset of the data to get approximate row counts or to break down full-table operations into smaller sub-tasks that can be run in parallel. ### Querying a subset of the data One use of `partition_hash` is to query a subset of the data and get approximate count of rows in the table. For example, suppose you have a table `t` with partitioning columns `(h1,h2)` as follows: ```sql create table t (h1 int, h2 int, r1 int, r2 int, v int, primary key ((h1, h2), r1, r2)); ``` You can use this function to query a subset of the data (in this case, 1/128 of the data) as follows: ```sql select count(*) from t where partition_hash(h1, h2) >= 0 and partition_hash(h1, h2) < 512; ``` The value `512` comes from dividing the full hash partition range by the number of subsets that you want to query (`65536/128=512`). ### Parallel full table scans To do a distributed scan, you can issue, in this case, 128 queries each using a different hash range as follows: ```sql .. where partition_hash(h1, h2) >= 0 and partition_hash(h1, h2) < 512; ``` ```sql .. where partition_hash(h1, h2) >= 512 and partition_hash(h1, h2) <1024 ; ``` and so on, till the last segment/range of `512` in the partition space: ```sql .. where partition_hash(h1, h2) >= 65024; ``` Refer to `partition_hash` in [Python 3](https://github.com/yugabyte/yb-tools/blob/main/ycql_table_row_count.py "Python 3") and [Go](https://github.com/yugabyte/yb-tools/tree/main/ycrc "Go") for full implementation of a parallel table scan. ## WriteTime function The `WriteTime` function returns the timestamp in microseconds when a column was written. For example, suppose you have a table `page_views` with a column named `views`: ```sql SELECT writetime(views) FROM page_views; writetime(views) ------------------ 1572882871160113 (1 rows) ``` ## TTL function The TTL function returns the number of seconds until a column or row expires. Assuming you have a table `page_views` and a column named `views`: ```sql SELECT TTL(views) FROM page_views; ttl(views) ------------ 86367 (1 rows) ``` ## See also - [All Expressions](/stable/api/ycql/#expressions "All Expressions")