The Data Studio

Hive: Date Extract Functions Do Not Work

Tested Using Hortonworks Data Platform (HDP) Sandbox, Release 2.5 with Hive 1.2.1 and Hive 2.1.0)

The Hive documentation describes the "extract " function for dates and timestamps and gives the following examples:

Examples:

  1. select extract(month from "2016-10-20") results in 10.
  2. select extract(hour from "2016-10-20 05:06:07") results in 5.
  3. select extract(dayofweek from "2016-10-20 05:06:07") results in 5.
  4. select extract(month from interval '1-3' year to month) results in 3.
  5. select extract(minute from interval '3 12:20:30' day to second) results in 20.

This has been in the documentation since Hive 1.2.1 with a promise that it would be implemented in a later release.

I expect documentation to describe what happens in the current release, and that future aspirations will appear only in marketing material, and then, hopefully, with a caveat to say that it is an aspiration. There was a caveat in the documentation but I missed it, and was disappointed when these functions did not work. So I was keen to see them working in Hive 2.1.0, but they still do not.

The caveat has been updated to say that they will be available from Hive 2.2.0. OK, but the function should not be in the current documentation if it is not available yet.

You can try the above queries and they will fail. That is what it says, so it is true, but it is not helpful.