Menu
Below query is my sql server query and I want it to convert it into hive query:
select DATEDIFF([minute], '19000101', '2013-01-01 10:10:10')
655
Solution. datediff function in Hive takes 2 dates in String type and gives you the difference between the dates.
If you need the difference in seconds (i.e.: you're comparing dates with timestamps, and not whole days), you can simply convert two date or timestamp strings in the format 'YYYY-MM-DD HH:MM:SS' (or specify your string date format explicitly) using unix_timestamp(), and then subtract them from each other to get the ...
Solution. CURRENT_DATE will give the current date and CURRENT_TIMESTAMP will give you the date and time. If you want to work with EPOCH time then use unix_timestamp() to get the EPOCH time and use from_unixtime to convert EPOCH to date and time.
Apache Hive does not support MINUS set operator. If you have any requirement to perform MINUS, then you have to rewrite your queries using an alternate method. There are two methods that you can use: Use LEFT OUTER JOIN.
You could use unix_timestamp for dates after 1970:
SELECT (unix_timestamp('2013-01-01 10:10:10')
- unix_timestamp('1970-01-01 00:00:00'))/60
1970-01-01
EDIT:
Adding Minutes: change date to unixtime -> add var * 60sec -> convert back to date
SELECT from_unixtime(unix_timestamp('2013-01-01 10:10:10') + 10 * 60) AS result
db<>fiddle demo using MySQL
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
