Apache Hive Cookbook

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...] ...