Rockset

    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'