CODEWITHSUNDEEP
mysqlsqlhivehiveqlsql-date-functions
Bhuvi007
Bhuvi007

Reputation: 111

Need to subtract some hours from given timestamp in hive

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?

Upvotes: 3

Views: 13717

Answers (3)

Sorul
Sorul

Reputation: 354

E.g. Your current time in your specific timezone - 1 hour:

select date_format(
        from_utc_timestamp(CURRENT_TIMESTAMP(),'Europe/Madrid') - INTERVAL 1 hours,
        'yyyy-MM-dd HH:mm:ss')
     as PREVIOUS_HOUR

Upvotes: 0

GoodDok
GoodDok

Reputation: 1850

In addition to the answer of @StrongYoung.

I find it very useful to define such long expressions as a macros and place in the initialization file (e.g. hive -i init-file.hql ...).

hive> create temporary macro sub_hours(dt string, hours int)
from_unixtime(unix_timestamp(dt, 'MM/dd/yyyy HH:mm:ss') - 3600 * hours, 'MM/dd/yyyy HH:mm:ss');
OK
Time taken: 0.005 seconds
hive> select sub_hours("01/01/2019 00:00:00", 5);
OK
12/31/2018 19:00:00
Time taken: 0.439 seconds, Fetched: 1 row(s)

Macros are available starting from Hive 0.12.0, further details can be found here (pay attention to the "bug fixes" section).

Upvotes: 4

StrongYoung
StrongYoung

Reputation: 772

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')

Upvotes: 9

Related Questions