CODEWITHSUNDEEP
mysqlsqlhiveunix-timestamphiveql
KD157
KD157

Reputation: 823

Hive Query Language Timestamp

I need to get the timestamp of interval of 7 days from the current time in milliseconds. I tried date_sub using now() but didn't work for me. How do we do this in hive. I need exactly the interval current_timestamp(unix) and interval of 7 days from the current in my query. Also is there any provision to select the time zone like UTC + 5:30 hrs like that?

Upvotes: 0

Views: 300

Answers (1)

Ran Locar
Ran Locar

Reputation: 561

I could not find information about millisecond based time calculations in HIVE.

unix_timestamp() is the current timestamp, but it does not have milliseconds.

The offset is 7 days*24 hours/day*3600 secs/hour = 604800 milliseconds

So the timestamp of the current time plus 7 days would be unix_timestamp() + 604800

The UTC part is trickier; you can use to_utc_timestamp, giving it your calculated timestamp, and the timezone it is coming from (as a date). It will return a date string, which you will pass through unix_timestamp()

In other words, assuming it is coming from PST, you should use:

select unix_timestamp(to_utc_timestamp(from_unixtime(unix_timestamp() + 604800), 'PST')) from dual;

See the documentation here:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

Upvotes: 1

Related Questions