Search code examples
datetimebigdatahiveqlhue

Calculate a date in behind 24 hours Hive

My demand is really so silly, so basically I need to go back in time 24 hours in a timestamp column in Hive.

So far, I have tried two different ways but it's not going thru:

 select 
 recordDate, --original date
 cast(date_sub(cast(recorddate as timestamp),1) as timestamp),  -- going one day behind without hour
cast((cast(cast(recorddate as timestamp) AS bigint)-1*3600) as timestamp)  -- crazy year
 from mtmbuckets.servpro_agents_events limit 10;

My output looks:

enter image description here

I appreciate the support you can give me.

thanks

Solution

  • There is not straight forward function in hive .

    1 Create UDF to do so .

    or

    Convert date in no of second and do you calculation( -24 *60*60) sec then change back int to data.

    use from_unixtime and unix_timestamp to achieve below code.

     select from_unixtime(unix_timestamp(recorddate) - 86400)
 from mtmbuckets.servpro_agen ts_events limit 10;;

    From_unixtime Convert time string with given pattern to Unix time stamp (in seconds) The result of this function is in seconds.

    Unix_timestamp

    Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds), using the default timezone and the default locale, return 0 if fail: unix_timestamp('2009-03-20 11:30:01') = 1237573801