Hive DateTime Functions – UNIX_TIMESTAMP() and FROM_UNIXTIME()

In this article, we will address the frequently utilized Hive date and time functions that convert the date to BIGINT and from BIGINT to the string or the desired date time format.

UNIX_TIMESTAMP():
This function returns the current date and current date time in seconds. The return value will be in BIGINT.

Note: This function is not deterministic and its value is not set for the scope of the query execution and thus avoids proper optimization of queries-which has been discontinued after 2.0 in favor of the constant CURRENT TIMESTAMP.

Examples: 
SELECT UNIX_TIMESTAMP();
The usage of the above statement should be avoided as UNIX_TIMESTAMP(void) is deprecated. Either CURRENT_DATE or CURRENT_TIMESTAMP should be used.

However it still calculates and returns the number of seconds between Unix epoch (1970-01-01 00:00:00 UTC) and the CURRENT_TIMESTAMP.

SELECT UNIX_TIMESTAMP(CURRENT_DATE);
The above command returns the total number of seconds between Unix epoch to current date.

SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP);
The above command returns the total number of seconds between Unix epoch to current date and time (this means as of now).

UnixTimeStamp

FROM_UNIXTIME():
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of “1970-01-01 00:00:00”. In other words, this function transforms the BIGINT value that has been converted from the date or date time back to the desired date (and time) format.

Examples:
SELECT FROM_UNIXTIME(1605898212);
Result:
2020-11-20 10:50:12

SELECT FROM_UNIXTIME(1605898235);
Result:
2020-11-20 10:50:35

SELECT FROM_UNIXTIME(1605898235, ‘dd/MM/yyyy hh:mm:ss’);
Result:
20/11/2020 10:50:35

SELECT FROM_UNIXTIME(1605898235, ‘yyyy-MM-dd’);
Result:
2020-11-20

SELECT FROM_UNIXTIME(1605898235, ‘yyyy:MM:dd’);
Result:
2020:11:20

From_UnixTime

10 comments

  1. 189971 541621Hi there, just became aware of your weblog by means of Google, and located that it is actually informative. Im gonna watch out for brussels. I will appreciate should you continue this in future. Lots of folks will likely be benefited from your writing. Cheers! 928012

    Like

    1. I spend a lot of time writing blog posts and frequently forget to express gratitude to my readers and followers. Your feedback is really valuable to me. Thanks a lot.

      Like

  2. I’ve been surfing online more than 4 hours today, yet I never found
    any interesting article like yours. It’s pretty worth enough for me.

    In my opinion, if all site owners and bloggers made good content as you did, the web will be a lot more useful than ever before.

    Like

    1. I spend a lot of time writing blog posts and frequently forget to express gratitude to my readers and followers.
      Your feedback is really valuable to me.
      Thanks a lot.

      Like

  3. Excellent article. Keep posting such kind of
    information on your site. Im really impressed by your blog.

    Hey there, You have done an incredible job. I will definitely digg it and for my part
    suggest to my friends. I am confident they’ll be benefited from
    this website.

    Like

    1. I spend a lot of time writing blog posts and frequently forget to express gratitude to my readers and followers.
      Your feedback is really valuable to me.
      Thanks a lot.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s