CODEWITHSUNDEEP
hadoophive
VoodooChild
VoodooChild

Reputation: 410

Hive timestamp comparison issue

I have a table where I want to filter out data based on the updated timestamp. I have a table test_table which has a timestamp column called "updated_timestamp". When I run :
select max(ZIW_UPDATED_TIMESTAMP) from test_table;
It returns : 2016-08-09 11:00:48.849 But when I run :
select count(*) from test_table where UPDATED_TIMESTAMP = from_unixtime(unix_timestamp('2016-08-09 11:00:48.849' , 'yyyy-MM-dd HH:mm:ss.SSS'));
it returns 0.

This exact timestamp is being used in ~5000 rows. I am not sure what is happening here.

Upvotes: 1

Views: 6096

Answers (1)

VoodooChild
VoodooChild

Reputation: 410

Just figured out a solution : Instead of using from_unixtime(unix_timestamp('2016-08-09 11:00:48.849' , 'yyyy-MM-dd HH:mm:ss.SSS')); I converted the timestamp column to unix timestamp column.
select count(*) from test_table where unix_timestamp( UPDATED_TIMESTAMP,'yyyy-MM-dd HH:mm:ss.SSS') = unix_timestamp('2016-08-09 11:00:48.849' , 'yyyy-MM-dd HH:mm:ss.SSS');
This works fine.

Upvotes: 1

Related Questions