Skip to main content
knarendran
knarendran
Gold ⭐️
April 1, 2017

Timestamp & String comparison in Hive

  • October 5, 2026 at 9:18 AM
  • 2 replies
  • 52848 views
  • Translate All

Issue:

Below is a sample test

CREATE DATABASE IF NOT EXISTS tmp;
DROP TABLE IF EXISTS tmp.orders;
CREATE TABLE tmp.orders (username STRING, order_creation_date TIMESTAMP, amount DOUBLE);
INSERT INTO TABLE tmp.orders VALUES ("jack", '2017-02-26 13:45:12', 88.2), ("jones", '2017-02-28 15:28:14', 92.4);

HDP 2.4.2

SELECT username FROM tmp.orders WHERE order_creation_date > '2017-02-27';
OK
jones
select * from tmp.orders;
OK
jack 2017-02-26 13:45:12 88.2
jones 2017-02-28 15:28:14 92.4

HDP 2.5.0 & 2.5.3

SELECT username FROM tmp.orders WHERE order_creation_date > '2017-02-27';
OK

SELECT username FROM tmp.orders WHERE order_creation_date > TO_DATE('2017-02-27');
OK

You can notice the above 2 queries did not display any result

Resolution:

Workaround: Use cast function in hive or include hh:mm:ss as below

SELECT username FROM tmp.orders WHERE order_creation_date > '2017-02-27 00:00:00';
OK
jones

This is a known bug in hive

Reference: Apache, Hortonworks

    2 replies

    hosako
    hosako
    Gold ⭐️
    April 3, 2017

    May I have apache jira ID?

    Translate
      ian_s_matthews
      ian_s_matthews
      Silver ⭐️
      September 22, 2017

      This is interesting. Thank you for posting this resolution to this comparison as I was able to use a similar approach to my issue!

      I am on HDP 2.6.1 and I had some similar unexpected behavior. When selecting a timestamp column, I wanted to grab all columns where the timestamp was within a certain date. However, when it returned the rows.... it "zeroed" the timestamp column to "yyyy-mm-dd 00:00:00" where ymd was the actual day. I found it bizarre since I only applied a function to my where clause and not my selected column. Here is an example:

      -- Unexpected query behavior
      SELECT time_stamp_column
      FROM my_table
      WHERE date(time_stamp_column) = '2017-09-15'
      limit 4;

      --Unexpected results (no timestamps in table with values "00:00:00 0")
      2017-09-15 00:00:00 0
      2017-09-15 00:00:00 0
      2017-09-15 00:00:00 0
      2017-09-15 00:00:00 0

      --Query using cast
      SELECT time_stamp_column
      FROM my_table
      WHERE time_stamp_column >= '2017-09-15 00:00:00' AND
      time_stamp_column < '2017-09-16 00:00:00'
      limit 4;

      --Expected/desired results (this is the actual data, as no columns have exactly the 00:00:00 timestamp)
      2017-09-15 17:25:28.766248
      2017-09-15 17:29:05.427199
      2017-09-15 17:29:08.219565
      2017-09-15 17:33:20.907088

      Thanks @knarendran !

      Hopefully someone else will find this useful.

      Translate
      Terms & ConditionsAccessibility statement