How to Subtract One Day from Current Date Then Convert to String in Hive

How to subtract one day from current date then convert to string in Hive

The problem is the way you trying to subtract a day from date.I would suggest to subtract number of seconds in a day(86400) from unix timestamp in where clause-

CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))as date) 

Subtracting days from current_timestamp() in Hive

Convert the current_timestamp to unix timestamp and subtract 10 days=10*86400 seconds. Then use from_unixtime to get the timestamp string.

from_unixtime(unix_timestamp(current_timestamp)-10*86400,'yyyy-MM-dd HH:mm:ss')

Note that unix_timestamp() is being deprecated but not unix_timestamp(string date)

How to calculate Date difference in Hive

datediff(to_date(String timestamp), to_date(String timestamp))

For example:

SELECT datediff(to_date('2019-08-03'), to_date('2019-08-01')) <= 2;

How to get 20 days before one date format:YYYYMMDD

The format you are using is wrong one. Upper/lower case matters a lot. Correct format is 'yyyyMMdd'.

Date_sub requires yyyy-MM-dd to work correctly, convert if necessary.

select date_sub(from_unixtime(unix_timestamp('20180912','yyyyMMdd')),20) ;
OK
2018-08-23

Cast as timestamp produces wrong result (1970 year), maybe it is an issue in my Hive version (1.2.1):

select cast(unix_timestamp('20180912','yyyyMMdd') as timestamp);
OK
1970-01-18 18:51:50.4

Use from_unixtime(unix_timestamp('20180912','yyyyMMdd')) for conversion, it works fine.

Calculate a date in behind 24 hours Hive

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

How to subtract months from date in HIVE

 select add_months('2015-02-01',-2);

if you need to go back to first day of the resulting month:

 select add_months(trunc('2015-02-01','MM'),-2);

---

Related Topics