Need to subtract some hours from given timestamp in hive

mysql sql hive hiveql sql-date-functions
Bhuvi007 picture Bhuvi007 · Nov 21, 2018 · Viewed 7.6k times · Source

Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')

Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37

I know that there is date_sub function in hive but it is only used to subtract days from the given timestamp. But I need to know if there is a way by which I can subtract hours or minutes or seconds.

I have also tried something like below as EDT timezone is 4 hours behind UTC (but getting wrong output): 

SELECT to_date(from_UTC_timestamp(unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')*1000, 'EST6EDT')) as earliest_date; -- OUTPUT: 2017-12-31 (wrong)

So can anyone help me out with this?

Answer

StrongYoung picture StrongYoung · Nov 21, 2018

It works fine.

select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss')

Related questions

select rows in sql with latest date for each ID repeated multiple times

I have a table where each ID is repeated 3 times. there is a date in front of each id in each row. I want to select entire row for each ID where date is latest. There are total 370 columns in …

Read More
Dynamic Partitioning + CREATE AS on HIVE

I'm trying to create a new table from another table with CREATE AS and dynamic Partitioning on HiveCLI. I'm learning from Hive official wiki where there is this example: CREATE TABLE T (key int, value string) PARTITIONED BY (ds string, …

Read More
How do I limit the number of rows per field value in SQL?

For example, I have a table like this in Hive: 1 1 1 4 1 8 2 1 2 5 3 1 3 2 and I want to only return the first two rows of each unique value of the first column. I want this to be able to limit the amount of data …

Read More