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/from anything other than string, UTC timezone is assumed. To use another timezone, convert the input/output timestamp to a datetime using AT TIME ZONE.

FromToSqlBlockResult
intfloatSELECT CAST(10 as float)10
intboolSELECT CAST(0 as bool)false
intstringSELECT CAST(10 as string)10
boolintSELECT CAST(true AS int)1
boolfloatSELECT CAST(true as float)1
boolstringSELECT CAST(true as string)true
floatintSELECT CAST(2.5 as int)2
floatboolSELECT CAST(2.5 as bool)true
floatstringSELECT CAST(2.5 as string)2.5
stringintSELECT CAST('2' AS int)2
stringfloatSELECT CAST('2.5' as float)2.5
stringboolSELECT CAST('false' as bool)false
stringbytesSELECT CAST('hello' as bytes)'aGVsbG8='
stringdateSELECT CAST('2018-05-26' AS date)2018-05-26
stringtimeSELECT CAST('10:30:20' AS time)10:30:20
stringdatetimeSELECT CAST('2018-05-26 10:30:20.345' as datetime)2018-05-26T10:30:20
stringtimestampSELECT CAST('2018-05-26 10:30:20.345Z' as timestamp)'2018-05-26T10:30:20.345000Z'
bytesstringSELECT CAST(bytes 'hello' as string)'hello'
datestringSELECT CAST(DATE(2018, 5, 26) as string)'2018-05-26'
timestringSELECT CAST(TIME(10, 30, 20) as string)'10:30:20'
datetimestringSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as string)'2018-05-26T10:30:20'
datetimetimestampSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as timestamp)'2018-05-26T10:30:20.000000Z'
timestampstringSELECT CAST(TIMESTAMP_SECONDS(1527373820) as string)'2018-05-26T22:30:20Z'
timestampdatetimeSELECT CAST(TIMESTAMP_SECONDS(1527373820) as datetime)2018-05-26T22:30:20
timestampdateSELECT CAST(TIMESTAMP_SECONDS(1527373820) as date)2018-05-26
timestamptimeSELECT CAST(TIMESTAMP_SECONDS(1527373820) as time)22:30:20
datetimedateSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as date)2018-05-26
datetimetimeSELECT CAST(DATETIME(2018, 5, 26, 10, 30, 20) as time)10:30:20
datedatetimeSELECT CAST(DATE(2018, 5, 26) as datetime)2018-05-26T00:00:00
datetimestampSELECT CAST(DATE(2018, 5, 26) as timestamp)'2018-05-26T00:00:00.000000Z'

More examples:

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.

SELECT
    TRY_CAST('foo' AS int)
null
SELECT
    TRY_CAST('2.0' AS int)
'null'

#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:

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.

SELECT
    TRY_STATIC_CAST('2' AS int)
null
SELECT
    TRY_STATIC_CAST(null AS bool)
null

Operator :: is a shortcut for TRY_STATIC_CAST:

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.

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'
Join us on Slack!
Building on Rockset? Come chat with us!