EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Hive TimeStamp
Secondary Sidebar
Hive Tutorial
  • Basics
    • Hive JDBC Driver
    • What is a Hive
    • Hive Architecture
    • Hive Installation
    • How To Install Hive
    • Hive Versions
    • Hive Commands
    • Hive Data Types
    • Hive Built-in Functions
    • Hive Function
    • Hive String Functions
    • Date Functions in Hive
    • Hive Table
    • Hive Drop Table
    • Hive Show Tables
    • Hive Group By
    • Hive Order By
    • Hive Cluster By
    • Joins in Hive
    • Hive Inner Join
    • Map Join in Hive
    • Hive nvl
    • Hive UDF
    • Dynamic Partitioning in Hive
    • HiveQL
    • HiveQL Queries
    • HiveQL Group By
    • Partitioning in Hive
    • Bucketing in Hive
    • Views in Hive
    • Indexes in Hive
    • External Table in Hive
    • Hive TimeStamp
    • Hive Database
    • Hive Interview Questions
    • Hive insert into

Related Courses

Hive Certification Course

Hadoop Course Training

All in One Data Science Course

Hive TimeStamp

By Priya PedamkarPriya Pedamkar

Hive TimeStamp

Introduction to Hive TimeStamp

Hive timestamps are an interpret to be time-zone less and stored as an offset in UNIX format. It is useful to get the updated information of tables at different internal changes to happen in the table. The hive timestamp format is YEAR-Month-Day-Hours-Minutes-seconds [YYYY-MM-DD HH:MM:SS] with an optional fraction of seconds. Anything else that will come with the above format should be read as a string and converted later.

In the hive, the timestamp plays a vital role in different applications like transactions to identify the changes or updates on the hive tables.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Note: The hive timestamp is only available with starting Hive 0.8.0 version.

Syntax:

Function name ( string date | string timestamp | string patter | string startdate | string enddate | string timezone | string fmt )

Note: In hive timestamp, we need to use the different timestamp or date function available in the hive and use it as per our requirement.

How does TimeStamp Work in Hive?

In the hive timestamp, it has the inbuilt functionality of timestamp, with the help of these predefined functions. We can work on the hive timestamp. It has the functionality to convert the hive timestamp to UNIX time format or form UNIX time format to hive timestamp. All the existing date time user definition functions like a month, day, year, hour, etc. are working with the TIMESTAMP data type.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,112 ratings)

The hive timestamp support below three conversions:

  • Integer numeric types: In integer numeric, we can interpret the UNIX timestamp in seconds
  • Floating-point numeric types: In floating-point numeric, we can interpret the UNIX timestamp in seconds with decimal format or precision
  • Strings type: In string type, we can interpret the JDBC compliant java.sql.Timestamp format “YYYY-MM-DD HH:MM: SS.fffffffff”. it considers 9 decimal place precision.
Note: The unix_timestamp() function is available in the hive. But it is providing the current timestamp in the second’s format. But the value is not fixed. Hence it is very difficult for optimizing the query and gets the related information. That’s why the unix_timestamp() function will deprecate from the version of hive 2.0.

Examples of HiveTimestamp

Following are the example are given below:

As discussed previously, we have the number of predefined functions available in the hive. Let’s explore one by one.

1. unix_timestamp()

The UNIX timestamp function is helpful to get the timestamp information in the second format. It is generally useful to calculate the time difference with a different application in Hadoop like YARN, HBase, etc.

The return data type of unix_timestamp function is “bigint”.

Query:

select unix_timestamp();

Output:

Hive TimeStamp-1.1

2. unix_timestamp(string date)

The UNIX timestamp with string date function helps convert the human-readable time or string date into seconds. It considers the local time zone (the time zone is using by the cluster or node). The string format should be in “YYYY – MM – DD – HH – MM – SS”.

The return data type of unix_timestampfunction is “bigint”.

Query:

select unix_timestamp('2020-03-25 16:32:01');

Output:

Hive TimeStamp-1.2

3. unix_timestamp(string date, string pattern)

The UNIX timestamp with string date and patter function is helpful to convert the timestamp in different patterns. As per our requirement, we can get the timestamp in seconds as per “YYYY – MM – DD” or “HH: MM”.

The return data type the unix_timestamp function is “bigint”.

Query – “YYYY – MM – DD” Format

select unix_timestamp(‘2020-03-25′,’yyyy-MM-dd’);

Output:

Hive TimeStamp-1.3

Query – “HH: MM” Format

select unix_timestamp('16:39','HH:mm');

Output:

Hive TimeStamp-1.4

4. to_date(string timestamp)

In the above function, we can get the complete date information form the timestamp. The timestamp format should in hive timestamp only.

The return data type of to_date function varies from the version of the hive. Suppose the version is below 2.1.0 then its string. If the version is above 2.1.0, then the data type will be “date”.

Query:

select to_date('2020-03-25 16:32:01');

Output:

Hive TimeStamp-1.5

5. year(string date)

With the help of the year function, we can fetch the year form string date. The return data type the year function is “int”.

Query:

select to_year('2020-03-25');

Output:

Hive TimeStamp-1.6

6. quarter(date | timestamp | string)

In the above function, we can get the quarter information form the timestamp. The return data type the quarterfunction is “int”.

Query:

select quarter('2020-03-25 16:32:01');

Output:

Hive TimeStamp-1.7

7. month(string date | timestamp)

In the above function, we can fetch the month information form the timestamp. The return data type the monthfunction is “int”.

Query:

select month(‘2020-03-25 16:32:01’);

Output:

Hive TimeStamp-1.8

8. hour(string date)

In the above function, we can fetch the hour information form the timestamp.

The return data type the hourfunction is “int”.

Query:

select hour('2020-03-25 16:32:01');

Output:

Hive TimeStamp-1.9

9. minute(string date)

In the above function, we can fetch the minute information form the timestamp.

The return data type the minutefunction is “int”.

Query:

select minute('2020-03-25 16:32:01');

Output:

Hive TimeStamp-1.10

10. weekofyear(string date)

With the help of weekofyear function, we can identify in which week the date belongs to.

The return data type the weekofyear function is “int”.

Query:

select weekofyear('2020-03-25 16:32:01');

Output:

Output-1.11

11. datediff(string enddate, string startdate)

With the help of datediff function, we will get the difference between two dates. The output would be in numeric format. The return data type the datediff function is “int”.

Query:

select datediff('2020-03-30', '2020-03-25');

Output:

Output-1.12

12. date_add(date |timestamp startdate, smallint |int days)

In the date_add function, we will get the next date with the respective integer values. We have provided in terms of days. The return data type the date_addfunction is “int”.

Query:

select date_add('2020-03-25 16:32:01', 1);

Output:

Output-1.13

13. date_sub(date | timestampstartdate, smallint |int days)

In the date_sub function, we will get the past date value for days integer value.

The return data type the date_sub function is “int”.

Query:

select date_sub('2020-03-25 16:32:01', 1);

Output:

Output-1.14

Advantages of using Hive Timestamp

  • The hive timestamp is useful to write the automation jobs.
  • Checks the updated time while inserting the record in the table.
  • Useful to comparison the timestamp with different services of Hadoop like YARN, HBase and other services.
  • Very useful in transaction applications, retail industry, etc.
  • It is helpful in troubleshooting and fixes the hive transactions jobs.

Conclusion

We have seen the uncut concept of “Hive Timestamp” with the proper example, explanation, syntax, SQL Query and different functions. With the help of “Hive Timestamp”, we will get the updated information of the hive’s table and current environment. Majorly it will use in automation script, transaction application, retail industry, etc.

Recommended Articles

This is a guide to Hive TimeStamp. Here we also discuss the Introduction, and how does timestamp work in a hive? Along with different examples and code implementation. You may also have a look at the following articles to learn more –

  1. Date Functions in Hive
  2. Hive Versions
  3. Hive Data Types
  4. Hive String Functions
Popular Course in this category
Hive Training (2 Courses, 5+ Projects)
  2 Online Courses |  5 Hands-on Projects |  25+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Hadoop Training Program (20 Courses, 14+ Projects, 4 Quizzes)4.9
All in One Data Science Bundle (360+ Courses, 50+ projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more