Type Functions

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ā†’ToSqlBlockResult
intā†’floatSELECT CAST(10 as float)10
intā†’boolSELECT CAST(0 as bool)false
intā†’stringSELECT CAST(10 as string)'10'
intā†’microsecond_intervalSELECT CAST(10 as microsecond_interval)MICROSECONDS(10)
intā†’month_intervalSELECT CAST(10 as month_interval)MONTHS(10)
boolā†’intSELECT CAST(true AS int)1
boolā†’floatSELECT CAST(true as float)1
boolā†’stringSELECT CAST(true as string)'true'
floatā†’intSELECT CAST(2.5 as int)2
floatā†’boolSELECT CAST(2.5 as bool)true
floatā†’stringSELECT CAST(2.5 as string)'2.5'
floatā†’microsecond_intervalSELECT CAST(2.5 as microsecond_interval)MICROSECONDS(2)
floatā†’month_intervalSELECT CAST(2.5 as month_interval)MONTHS(2)
stringā†’intSELECT CAST('2' AS int)2
stringā†’floatSELECT CAST('2.5' as float)2.5
stringā†’boolSELECT CAST('false' as bool)false
stringā†’bytesSELECT CAST('hello' as bytes)'aGVsbG8='
stringā†’dateSELECT CAST('2018-05-26' AS date)2018-05-26
stringā†’timeSELECT CAST('10:30:20' AS time)10:30:20
stringā†’datetimeSELECT CAST('2018-05-26 10:30:20.345' as datetime)2018-05-26T10:30:20.345
stringā†’timestampSELECT CAST('2018-05-26 10:30:20.345Z' as timestamp)'2018-05-26T10:30:20.345000Z'
bytesā†’stringSELECT CAST(bytes 'hello' as string)'hello'
dateā†’stringSELECT CAST(DATE(2018, 5, 26) as string)'2018-05-26'
timeā†’stringSELECT CAST(TIME(10, 30, 20) as string)'10:30:20'
datetimeā†’stringSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as string)'2018-05-26T10:30:20'
datetimeā†’timestampSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as timestamp)'2018-05-26T10:30:20.000000Z'
timestampā†’stringSELECT CAST(TIMESTAMP_SECONDS(1527373820) as string)'2018-05-26T22:30:20Z'
timestampā†’datetimeSELECT CAST(TIMESTAMP_SECONDS(1527373820) as datetime)2018-05-26T22:30:20
timestampā†’dateSELECT CAST(TIMESTAMP_SECONDS(1527373820) as date)2018-05-26
timestampā†’timeSELECT CAST(TIMESTAMP_SECONDS(1527373820) as time)22:30:20
datetimeā†’dateSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as date)2018-05-26
datetimeā†’timeSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as time)10:30:20
dateā†’datetimeSELECT CAST(DATE(2018, 5, 26) as datetime)2018-05-26T00:00:00
dateā†’timestampSELECT CAST(DATE(2018, 5, 26) as timestamp)'2018-05-26T00:00:00.000000Z'
microsecond_intervalā†’intSELECT CAST(MICROSECONDS(10) as int)10
microsecond_intervalā†’floatSELECT CAST(MICROSECONDS(10) as float)10.0
month_intervalā†’intSELECT CAST(MONTHS(10) as int)10
month_intervalā†’floatSELECT CAST(MONTHS(10) as float)10.0

More examples:

SQL commandResult
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 commandResult
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.

FromToQueryResult
intfloatSELECT STATIC_CAST(10 as float)10
intboolSELECT STATIC_CAST(0 as bool)false
boolintSELECT STATIC_CAST(true AS int)1
boolfloatSELECT STATIC_CAST(true AS float)1
floatintSELECT CAST(2.5 as int)2
floatboolSELECT STATIC_CAST(3.5 AS bool)true
stringbytesSELECT STATIC_CAST('hello' as bytes)'aGVsbG8='
bytesboolSELECT STATIC_CAST(bytes'hello' as bool)true
bytesstringSELECT CAST(bytes 'hello' as string)'hello'

More examples:

SQL commandResult
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 commandResult
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 commandResult
SELECT '2'::intnull
SELECT 3.7::int3

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.

TypeQueryResult
intSELECT TYPEOF(10)'int'
floatSELECT TYPEOF(2.0)'float'
boolSELECT TYPEOF(true)'bool'
stringSELECT TYPEOF('hello')'string'
bytesSELECT TYPEOF(bytes 'hello')'bytes'
undefinedSELECT TYPEOF(undefined)'undefined'
nullSELECT TYPEOF(null)'null'
arraySELECT TYPEOF(ARRAY_CREATE(10, 20, 30))'array'
objectSELECT TYPEOF(OBJECT(ARRAY_CREATE('field1'), ARRAY_CREATE('hello')))'object'
dateSELECT TYPEOF(DATE(2018, 5, 26))'date'
timeSELECT TYPEOF(TIME(10, 30, 20))'time'
datetimeSELECT TYPEOF(DATETIME(2018, 5, 26, 10, 30, 20))'datetime'
timestampSELECT TYPEOF(TIMESTAMP_SECONDS(1527373820))'timestamp'
month_intervalSELECT TYPEOF(INTERVAL 5 MONTH)'month_interval'
microsecond_intervalSELECT TYPEOF(INTERVAL 3 HOUR)'microsecond_interval'

List of functions defined in this section:

FunctionDescription
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.