Using Date/Time data types

Hive supports two data types for Date/Time-related fields—Timestamp and Date:

The Timestamp data type is used to represent a particular time with the date and time value. It supports variable-length encoding of the traditional UNIX timestamp with an optional nanosecond precision.

It supports different conversions. The timestamp value provided as an integer numeric type is interpreted as a UNIX timestamp in seconds; a timestamp value provided as a floating point numeric type is interpreted as a UNIX timestamp in seconds with decimal precision; the timestamp value provided as string is interpreted as the java.sql.Timestamp format YYYY-MM-DD HH:MM:SS.fffffffff.

If the timestamp value is in another format than yyyy-mm-dd hh:mm:ss[.f...], then UDF can be used to convert them to the timestamp format. The Date type is used to represent only the date part of timestamp, that is, YYYY-MM-DD. This type doesn't represent the time of day component. The Date ranges allowed are...