DATE Data Type

Use the DATE data type to store date values. The DATE type is supported for Avro, HBase, Kudu, Parquet, and Text.

Range:

0001-01-01 to 9999-12-31

Literals and expressions:

The DATE literals are in the form of DATE'YYYY-MM-DD'. For examplep, DATE '2013-01-01'

Parquet and Avro considerations:

Parquet and Avro use DATE logical type for dates. The DATE logical type annotates an INT32 that stores the number of days from the Unix epoch, January 1, 1970. This representation introduces an interoperability issue between Impala and older versions of Hive:

If Hive versions lower than 3.1 wrote dates earlier than 1582-10-15 to a Parquet or Avro table, those dates would be read back incorrectly by Impala and vice versa. In Hive 3.1 and higher, this is no longer an issue.

Explicit casting between DATE and other data types:

DATE type can only be converted to/from DATE, TIMESTAMP, or STRING types as described below.

Cast fromCast toResult
TIMESTAMPDATEThe date component of the TIMESTAMP is returned, and the time of the day component of the TIMESTAMP is ignored.
STRINGDATEThe DATE value of yyyy-MM-dd is returned.

The STRING value must be in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSSSSSSS pattern.

If the time component is present in STRING, it is silently ignored.

If the STRING value does not match the above formats, an error is returned.

DATETIMESTAMPThe year, month, and day of the DATE is returned along with the time of day component set to 00:00:00.
DATESTRINGThe STRING value, ‘yyyy-MM-dd’, is returned.

Implicit casting between DATE and other types:

Implicit casting is supported:

  • From STRING to DATE if the source STRING value is in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSSSSSSS pattern.
  • From DATE to TIMESTAMP.

Added in:

The DATE type is available in Impala 3.3 and higher.

Kudu considerations:

In Impala 3.4, you can read and write DATE values to Kudu tables.

Parent topic: Data Types