date with 23:59:59 in hive

Question

I've got to pull the records between yesterday date starting 00:00:00 to end of hour 23:59:59.

I got the yesterday date as follows

select from_unixtime(unix_timestamp()-1*60*60*24, 'dd-MMM-yy');

However, I am not sure how to get to 23:59:59 in hive.

As per this SO ( 1 year old) answer , it is achievable using hive UDF.

I wonder if there is a simple way such as using a build in function to do so.

Answer 1

We need only playing with the unixtime

select 
from_unixtime(datediff(current_date, '1970-01-01')*3600*24-3600, 'dd-MM-yyyy HH:mm:ss') 
as start_time,
from_unixtime(datediff(current_date, '1970-01-00')*3600*24-3600-1, 'dd-MM-yyyy HH:mm:ss') 
as end_time

And we get: