This page covers type functions in Rockset. For more on Rockset types, check out the data types page.
To cast values from one type to another, you can use one of four cast functions: CAST, TRY_CAST, STATIC_CAST and TRY_STATIC_CAST.
CAST
CAST(x AS type) Lexical cast that supports casting between more types than STATIC CAST. Errors the query if the cast is not supported.
Supported lexical casts are shown in the table below.
Note: For casts involving timestamps to or from anything other than string, UTC timezone is assumed.
To use different timezone, convert the input/output timestamp to a datetime using
AT TIME ZONE.
| From→To | SqlBlock | Result |
|---|---|---|
| int→float | SELECT CAST(10 as float) | 10 |
| int→bool | SELECT CAST(0 as bool) | false |
| int→string | SELECT CAST(10 as string) | '10' |
| int→microsecond_interval | SELECT CAST(10 as microsecond_interval) | MICROSECONDS(10) |
| int→month_interval | SELECT CAST(10 as month_interval) | MONTHS(10) |
| bool→int | SELECT CAST(true AS int) | 1 |
| bool→float | SELECT CAST(true as float) | 1 |
| bool→string | SELECT CAST(true as string) | 'true' |
| float→int | SELECT CAST(2.5 as int) | 2 |
| float→bool | SELECT CAST(2.5 as bool) | true |
| float→string | SELECT CAST(2.5 as string) | '2.5' |
| float→microsecond_interval | SELECT CAST(2.5 as microsecond_interval) | MICROSECONDS(2) |
| float→month_interval | SELECT CAST(2.5 as month_interval) | MONTHS(2) |
| string→int | SELECT CAST('2' AS int) | 2 |
| string→float | SELECT CAST('2.5' as float) | 2.5 |
| string→bool | SELECT CAST('false' as bool) | false |
| string→bytes | SELECT CAST('hello' as bytes) | 'aGVsbG8=' |
| string→date | SELECT CAST('2018-05-26' AS date) | 2018-05-26 |
| string→time | SELECT CAST('10:30:20' AS time) | 10:30:20 |
| string→datetime | SELECT CAST('2018-05-26 10:30:20.345' as datetime) | 2018-05-26T10:30:20.345 |
| string→timestamp | SELECT CAST('2018-05-26 10:30:20.345Z' as timestamp) | '2018-05-26T10:30:20.345000Z' |
| bytes→string | SELECT CAST(bytes 'hello' as string) | 'hello' |
| date→string | SELECT CAST(DATE(2018, 5, 26) as string) | '2018-05-26' |
| time→string | SELECT CAST(TIME(10, 30, 20) as string) | '10:30:20' |
| datetime→string | SELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as string) | '2018-05-26T10:30:20' |
| datetime→timestamp | SELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as timestamp) | '2018-05-26T10:30:20.000000Z' |
| timestamp→string | SELECT CAST(TIMESTAMP_SECONDS(1527373820) as string) | '2018-05-26T22:30:20Z' |
| timestamp→datetime | SELECT CAST(TIMESTAMP_SECONDS(1527373820) as datetime) | 2018-05-26T22:30:20 |
| timestamp→date | SELECT CAST(TIMESTAMP_SECONDS(1527373820) as date) | 2018-05-26 |
| timestamp→time | SELECT CAST(TIMESTAMP_SECONDS(1527373820) as time) | 22:30:20 |
| datetime→date | SELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as date) | 2018-05-26 |
| datetime→time | SELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as time) | 10:30:20 |
| date→datetime | SELECT CAST(DATE(2018, 5, 26) as datetime) | 2018-05-26T00:00:00 |
| date→timestamp | SELECT CAST(DATE(2018, 5, 26) as timestamp) | '2018-05-26T00:00:00.000000Z' |
| microsecond_interval→int | SELECT CAST(MICROSECONDS(10) as int) | 10 |
| microsecond_interval→float | SELECT CAST(MICROSECONDS(10) as float) | 10.0 |
| month_interval→int | SELECT CAST(MONTHS(10) as int) | 10 |
| month_interval→float | SELECT CAST(MONTHS(10) as float) | 10.0 |
More examples:
| SQL command | Result |
|---|---|
SELECT CAST('foo' AS int) | Error |
TRY_CAST
TRY_CAST(x AS type) Same as CAST, except it doesn't error out the query for an unsupported cast; a null is returned instead.
| SQL command | Result |
|---|---|
SELECT TRY_CAST('foo' AS int) | null |
SELECT TRY_CAST('2.0' AS int) | 2 |
STATIC_CAST
STATIC_CAST(x AS type) Static cast that converts values from one data type to another. Supports casts between numeric types and casts to bool with Python semantics. Errors the query if the cast is not supported.
Supported static casts are show below.
| From | To | Query | Result |
|---|---|---|---|
| int | float | SELECT STATIC_CAST(10 as float) | 10 |
| int | bool | SELECT STATIC_CAST(0 as bool) | false |
| bool | int | SELECT STATIC_CAST(true AS int) | 1 |
| bool | float | SELECT STATIC_CAST(true AS float) | 1 |
| float | int | SELECT CAST(2.5 as int) | 2 |
| float | bool | SELECT STATIC_CAST(3.5 AS bool) | true |
| string | bytes | SELECT STATIC_CAST('hello' as bytes) | 'aGVsbG8=' |
| bytes | bool | SELECT STATIC_CAST(bytes'hello' as bool) | true |
| bytes | string | SELECT CAST(bytes 'hello' as string) | 'hello' |
More examples:
| SQL command | Result |
|---|---|
SELECT STATIC_CAST(0.0 AS bool) | false |
SELECT STATIC_CAST(bytes'' as bool) | false |
SELECT STATIC_CAST('2' AS int) | Error |
SELECT STATIC_CAST(null AS bool) | null |
TRY_STATIC_CAST
TRY_STATIC_CAST(x AS type) Same as STATIC_CAST, except it doesn't error out the query for an unsupported cast; a null is returned instead.
| SQL command | Result |
|---|---|
SELECT TRY_STATIC_CAST('2' AS int) | null |
SELECT TRY_STATIC_CAST(null AS bool) | null |
Operator :: is a shortcut for TRY_STATIC_CAST:
| SQL command | Result |
|---|---|
SELECT '2'::int | null |
SELECT 3.7::int | 3 |
IS_SCALAR
IS_SCALAR(x) Returns true if x is a scalar, which is any type except array, object, undefined and null.
TYPEOF
TYPEOF(x) Returns a string, name of the type of x.
The following table shows what TYPEOF returns for each of the supported data types.
| Type | Query | Result |
|---|---|---|
| int | SELECT TYPEOF(10) | 'int' |
| float | SELECT TYPEOF(2.0) | 'float' |
| bool | SELECT TYPEOF(true) | 'bool' |
| string | SELECT TYPEOF('hello') | 'string' |
| bytes | SELECT TYPEOF(bytes 'hello') | 'bytes' |
| undefined | SELECT TYPEOF(undefined) | 'undefined' |
| null | SELECT TYPEOF(null) | 'null' |
| array | SELECT TYPEOF(ARRAY_CREATE(10, 20, 30)) | 'array' |
| object | SELECT TYPEOF(OBJECT(ARRAY_CREATE('field1'), ARRAY_CREATE('hello'))) | 'object' |
| date | SELECT TYPEOF(DATE(2018, 5, 26)) | 'date' |
| time | SELECT TYPEOF(TIME(10, 30, 20)) | 'time' |
| datetime | SELECT TYPEOF(DATETIME(2018, 5, 26, 10, 30, 20)) | 'datetime' |
| timestamp | SELECT TYPEOF(TIMESTAMP_SECONDS(1527373820)) | 'timestamp' |
| month_interval | SELECT TYPEOF(INTERVAL 5 MONTH) | 'month_interval' |
| microsecond_interval | SELECT TYPEOF(INTERVAL 3 HOUR) | 'microsecond_interval' |
List of functions defined in this section:
| Function | Description |
|---|---|
CAST(x AS type) | Lexical cast that supports casting between more types than STATIC CAST. Errors the query if the cast is not supported. |
IS_SCALAR(x) | Returns true if x is a scalar, which is any type except array, object, undefined and null. |
STATIC_CAST(x AS type) | Static cast that converts values from one data type to another. Supports casts between numeric types and casts to bool with Python semantics. Errors the query if the cast is not supported. |
TRY_CAST(x AS type) | Same as CAST, except it doesn't error out the query for an unsupported cast; a null is returned instead. |
TRY_STATIC_CAST(x AS type) | Same as STATIC_CAST, except it doesn't error out the query for an unsupported cast; a null is returned instead. |
TYPEOF(x) | Returns a string, name of the type of x. |