Impala type conversion functions

Conversion functions are usually used in combination with other functions, to explicitly pass the expected data types. Impala has strict rules regarding data types for function parameters. For example, Impala does not automatically convert a DOUBLE value to FLOAT, a BIGINT value to INT, or other conversion where precision could be lost or overflow could occur. Also, for reporting or dealing with loosely defined schemas in big data contexts, you might frequently need to convert values to or from the STRING type.

Function reference:

Impala supports the following type conversion functions:

CAST(expression AS type)
Purpose: Returns expression converted to the type data type.
If the expression value is of a type that cannot be converted to the target type:
  • Of DECIMAL, DATE, and BOOLEAN, the function returns an error.
  • Of all other types, the function returns NULL.

Usage notes:

Use CAST when passing a column value or literal to a function that expects a parameter with a different type. Frequently used in SQL operations such as CREATE TABLE AS SELECT and INSERT ... VALUES to ensure that values from various sources are of the appropriate type for the destination columns.

Where practical, do a one-time CAST() operation during the ingestion process to make each column into the appropriate type, rather than using many CAST() operations in each query; doing type conversions for each row during each query can be expensive for tables with millions or billions of rows.

The way this function deals with time zones when converting to or from TIMESTAMP values is affected by the ‑‑use_local_tz_for_unix_timestamp_conversions startup flag for the impalad daemon. See TIMESTAMP Data type for details about how Impala handles time zone considerations for the TIMESTAMP data type.

CAST(expression AS type FORMAT pattern)
Purpose: Returns expression converted to the type data type based on the pattern format string. This signature of CAST() with the FORMAT clause is supported only for casts between STRING / CHAR / VARCHAR types and TIMESTAMP / DATE types.
The following rules apply to pattern. Any exceptions to these rules are noted in the Details column of the table below.
  • pattern is a case-insensitive STRING unless noted otherwise in the table below.
  • If pattern is NULL, an empty string, or a number, an error returns.
  • A fewer digits in expression than specified in the pattern is accepted if a separator is correctly specified in the pattern. For example, CAST('5-01-2017' AS DATE FORMAT 'MM-dd-yyyy') returns DATE'2017-05-01'.
  • If fewer number of digits are in expression than specified in the pattern, the current date is used to complete the year pattern. For example, CAST('19/05' AS DATE FORMAT 'YYYY/MM') will return DATE'2019-05-01' when executed on August 8, 2019.
The following format patterns are supported in the FORMAT clause.
Pattern Description Details
YYYY 4-digit year.
YYY Last 3 digits of a year.
YY Last 2 digits of a year.
Y Last digit of a year
RRRR 4-digit round year

If 1, 3 or 4-digit year values are provided in expression, treated as YYYY.

If 2-digit years are provided in expression, treated as RR.

For date/time to string conversions, treated as YYYY.

If YYYY, YYY, YY, Y, or RR is given in the same pattern for a string to date/time conversion, an error returns.

RR 2-digit round year.

For date/time to string conversion, same as YY.

For string to date/time conversions, the first 2 digits of the year in the return value depends on the specified two-digit year and the last two digits of the current year as follows:
  • If the specified 2-digit year is 00 to 49:

    • If the last 2 digits of the current year are 00 to 49, the returned year has the same first 2 digits as the current year.

    • If the last 2 digits of the current year are 50 to 99, the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

  • If the specified 2-digit year is 50 to 99:

    • If the last 2 digits of the current year are 00 to 49, the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

    • If the last 2 digits of the current year are 50 to 99, the returned year has the same first 2 digits as the current year.

If YYYY, YYY, YY, Y, or RR is given in the same pattern for a string to date/time conversion, an error returns.

If 1-digit year values are provided in expression, it is treated as YYYY.

Q Quarter of year (1-4)

Not supported in string to date/time conversions.

MM Month

In date/time to string conversions, 1-digit months are prefixed with a zero.

MONTH

Month

month

Full month name In string to date/time conversions:
  • Converts a textual month name to a 2-digit month number.
  • The input strings are expected without trailing spaces, e.g. "June", "August".
  • If the FX modifier is active for the token, a 9-character-long full month name with trailing spaces is expected for the input string.
In date/time to string conversions:
  • Converts to a 9-character-long full month name with trailing spaces.
  • If the FM modifier is active for this token, converts to a month name without trailing spaces.

This pattern token is case sensitive. MONTH, Month, month would require different casing of the input in string to date/time conversions and would produce cased outputs in date/time to string conversions, For example, MONTH, Month, month would convert respectively JULY, July, and july.

MON

Mon

mon

Abbreviated 3-character month name

This pattern token is case sensitive. MON, Mon, mon would require different casing of the input in string to date/time conversions and would produce cased outputs in date/time to string conversions, For example, MON, Mon, mon would convert respectively JUL, Jul, and jul.

WW Week of year (1-53)

Not supported in string to date/time conversions.

The 1st week begins on January 1st and ends on January 7th.

W Week of month (1-5) Week of month (1-5)
D Day of week (1-7) Day of week (1-7)
DD Day of month (1-31)

In date/time to string conversions, a 1-digit day is prefixed with a zero.

DDD Day of year (1-366)

In string to date/time conversions, providing MM and DD along with DDD results an error, e.g. YYYY-MM-DDD.

DAY

Day

day

Day name

Not supported in string to date/time conversions.

In date/time to string conversions:
  • Converts to a 9-character-long full day name with trailing spaces.
  • If the FM modifier is active for this token, converts to a day name without trailing spaces.

This pattern token is case sensitive. DAY, Day, day would produce cased outputs in date/time to string conversions, For example, DAY, Day, day would convert respectively MONDAY, Monday, and monday.

DY

Dy

dy

Not supported in string to date/time conversions.

This pattern token is case sensitive. DY, Dy, dy would produce cased outputs in date/time to string conversions, For example, DY, Dy, dy would convert respectively MON, Mon, and mon.

HH

HH12

Hour of day (1-12)

In date/time to string conversions, 1-digit hours are prefixed with a zero.

If provided hour in expression is not between 1 and 12, returns an error.

If no AM/PM is provided in expression, the default is AM.

HH

HH12

Hour of day (1-12)

In date/time to string conversions, 1-digit hours are prefixed with a zero.

If provided hour in expression is not between 1 and 12, returns an error.

If no AM/PM is provided in expression, the default is AM.

HH24 Hour of day (0-23)

In string to date/time conversions, if HH12, AM, PM are given in the same pattern, an error returns.

MI Minute of hour (0-59)

In date/time to string conversions, 1-digit minutes are prefixed with a zero.

SS Second of minute (0-59)

In date/time to string conversions, 1-digit seconds are prefixed with a zero.

SSSSS Second of Day (0-86399) In string to timestamp conversions, if SS, HH, HH12, HH24, MI, AM/PM are given in the same pattern, an error returns.
FF

FF1, ..., FF9

Fractional second

A number, 1 to 9, can be used to indicate the number of digits.

FF specifies a 9 digits fractional second.

AM

PM

A.M.

P.M.

Meridiem indicator

For date/time to string conversions, AM and PM are treated as synonyms. For example, casting '2019-01-01 11:00 am' to TIMESTAMP with the 'YYYY-MM-DD HH12:MI PM' pattern returns 01-JAN-19 11.00.00.000000 AM.

For string to date/time conversion, HH24 in the same pattern returns an error.

TZH Timezone offset hour

An optional sign, + or -, and 2 digits for the value of signed numbers are allowed for the source expression, e.g. +10, -05, 04.

The allowed values are from -15 to +5.

TZM Timezone offset minute

Unsigned numbers are allowed for the source expression.

The allowed values are from 0 to 59.

-

.

/

,

'

;

:

<space>

Separator

For string to date/time conversions, any separator character in the pattern string would match any separator character in the input expression.

For example, CAST(“20191010” AS DATE FORMAT “YYYY-MM-DD”) returns an error, but CAST("2019-.;10 10" AS DATE FORMAT "YYYY-MM-DD") succeeds.

T Separates the date from the time. This pattern is used for accepting ISO 8601 date/time formats.

Example: YYYY-MM-DDTHH24:MI:SS.FF9Z

Z Indicates the zero hour offset from UTC. This pattern is used for accepting ISO 8601 date/time formats.
FX Forces an exact match between the format pattern, pattern, and the input argument, expression.

Must be specified at the beginning of the format pattern and is valid for the whole pattern.

In string to date/time conversions:

  • Forces strict separator matching, including the separator character.
  • Expects all the tokens to have the maximum possible length.

In date/time to string conversions, the outputs are padded as follows:

  • Numeric outputs are left padded with zeros.
  • Text outputs are right padded with spaces.
FM Overrides FX.

In a date/time to string conversions, FM suppresses blank padding for the element immediately following the FM in the pattern string, e.g. "2010-1-9".

In string to date/time conversions, FM is used to override the effect of FX for certain tokens as follows:
  • The length of the token in the input argument can be shorter than the max length of that token type if followed by a separator, e.g. 1-digit month, less than 4-digit year.

  • FM modifier skips the separators and affects the next non-separator token. For example, CAST('1999-10-2' AS TIMESTAMP FORMAT 'FXYYYY-MMFM-DD') returns 1999-10-02 00:00:00 as FM is applied to DD and not to the separator(-).

Free text Nested string

You can specify a free text with surrounding double quotes (") in pattern where the same text is expected in the input argument without surrounding double quotes.

In date/time to string conversions, the string is included in the output with the characters' case preserved.

In string to date/time conversions, the nested string is not included in the resulting date/time object. However, the nested string has to match the related part of the input string, including the case.

The following rules apply:
  • If the pattern is surrounded by double quotes, double quotes surrounding the free text token must be escaped with a single backslash (\").

    For example: CAST('"Date:"2019-11-10' AS DATE FORMAT "\"Date:\"YYYY-MM-DD")

  • If the pattern is surrounded by double quotes, and there is an escaped double quotes inside a text token that is itself surrounded by escaped double quotes, escape the double quotes in the free text token with a triple backslash: (\\\")

    For example: CAST("1985 some \"text 11-28" AS DATE''' FORMAT "YYYY\" some \\\"text \"MM-DD")

  • If the pattern is surrounded by single quotes, the free text token can be surrounded by (non-escaped) double quotes. To include double quotes within the free text token, those double quotes must be escaped by a single backslash.

    For example: CAST('"Date:"2019-11-10' AS DATE FORMAT '"\"Date:\""YYYY-MM-DD')

  • Literal single quotes/apostrophes (') in the nested string must be escaped with a single backslash if the whole pattern string is delimited by single quotes.

  • If a free text token contains any separator characters in the beginning, and the text token is right after a separator token sequence, an error returns as it's not trivial to find where the separator sequence ends and where the free text starts. In this case, use FX for strict matching.

    For example: CAST("1985-11- ' 10" AS DATE FORMAT "YYYY-MM-\" ' \"DD") returns an error, but removing the dash before the text token makes this succeed.

Examples:

Input Output
CAST("02-11-2014" AS DATE FORMAT 'dd-mm-yyyy')
2014-11-02
CAST("365 2014" AS DATE FORMAT 'DDD-YYYY')
2014-12-31
CAST("5-01-26" AS DATE FORMAT 'YY-MM-DD')
Executed at 2019-01-01 11:11:11:

2015-01-26

CAST('2018-11-10T15:11:04Z' 
   AS TIMESTAMP
   FORMAT 'YYYY-MM-DDTHH24:MI:SSZ')
2018-11-10 15:11:04
CAST("95-01-28" AS DATE FORMAT 'YYY-MM-DD')
Executed at 2019-01-01 11:11:11:

2095-01-28

CAST("49-01-15" AS DATE FORMAT 'RR-MM-DD')
Round year when last 2 digits of current year is greater than 49.

Executed at 2099-01-01 11:11:11:

2149-01-15

CAST('2019.10.10 13:30:40.123456 +01:30'
   AS TIMESTAMP
   FORMAT 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM')
2019-10-10 13:30:40.123456000
CAST('2001-03-01 03:10:15.123456 -01:30'
   AS TIMESTAMP
   FORMAT 'FXYYYY-MM-DD HH12:MI:SS.FF6 TZH:TZM')
2001-03-01 03:10:15.123456000
CAST('2001-03-02 03:10:15'
   AS TIMESTAMP
   FORMAT 'FXYYYY MM-DD HH12:MI:SS')
NULL because the separator between the year and month do not match.
CAST('2001-3-05'
   AS TIMESTAMP
   FORMAT 'FXYYYY-MM-DD')
NULL because the length of the month token does not match the pattern.
CAST('2001-3-11 3:15:00.12345'
   AS TIMESTAMP
   FORMAT 'FXYYYY-FMMM-DD FMHH12:MI:SS.FMFF')
2001-03-11 03:15:00.123450000

The multiple FM modifiers for the month and hour override the FX and suppress padding.

CAST('2019-01-01 01:01:01'
   AS TIMESTAMP
   FORMAT 'FMHH12:MI:FMSS')
1:01:1
CAST('Date: 2019-11-10'
   AS DATE
   FORMAT '"Date: "YYYY-MM-DD')
2019-11-10
Allow implicit casts between numeric and string types when inserting into table

The current implementation requires explicit casts for numeric and string-based literals, however this release relaxes the implicit casting rules for these cases. This is controlled through a query option allow_unsafe_casts and is turned off by default. This query option allows implicit casting between some numeric types and string types.

Possibilities:
From To
String Float
String Double, Real
String Tinyint
String Smallint
String Int
String Bigint
Float String
Double, Real String
Tinyint String
Smallint String
Int String
Bigint String

Example

In the following example, numeric and string literals are inserted into a FLOAT column. The assumption is that all available numeric types and string types should be interchangeable when they are listed as literals in insert statements.

  • String to Float column:
    • INSERT INTO example(float_col) VALUES ("041241");
  • Float to String column:
    • INSERT INTO example(string_col) VALUES (CAST(123.0 AS FLOAT);
  • Integer to String column:
    • INSERT INTO example(string_col) VALUES (10000000);
  • String and Integer literals to String column:
    • INSERT INTO example(STRING_COL) VALUES (123), ("example");

Casting rules

A hand-written query containing different types qualifies for implicit casting, but a referenced column does not. All available numeric types and string types are interchangeable when they are listed as literals in insert statements.

The insert statements and set operations accept unsafe implicitly casted expressions when the source expressions are constant.

The following implicit type casts are enabled in unsafe mode:
  • String -> Float, Double
  • String -> Tinyint, Smallint, Int, Bigint
  • Float, Double -> String
  • Tinyint, Smallint, Int, Bigint -> String
TYPEOF(expression)
Purpose: Returns the name of the data type corresponding to expression. For types with extra attributes, such as length for CHAR and VARCHAR, or precision and scale for DECIMAL, includes the full specification of the type.

Return type: STRING

Usage notes: Typically used in interactive exploration of a schema, or in application code that programmatically generates schema definitions such as CREATE TABLE statements, for example, to get the type of an expression such as col1 / col2 or CONCAT(col1, col2, col3). This function is especially useful for arithmetic expressions involving DECIMAL types because the precision and scale of the result is can be different than that of the operands.

Examples:

TYPEOF(2) returns TINYINT.

TYPEOF(NOW()) returns TIMESTAMP.