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→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'