EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Date Functions in Hive
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

Date Functions in Hive

By Priya PedamkarPriya Pedamkar

date function in hive

Introduction to Date Functions in Hive

Hive provides Date Functions that help us in performing different operations on date and date data types. Hive has a wide variety of built-in date functions similar. Date functions in Hive are almost like date functions in RDBMS SQL. Date functions are used for processing and manipulating data types. In this article, we will discuss various Date Functions provided by Hive in detail with multiple examples.

Syntax

Data Types in Hive are very complicated and highly formatted too. Every date value holds multiple information like a second, minute, hour, day, week, month, year, century, etc., all these date data can be manipulated using Hive built-in date functions.

  • current_date(): This function returns the current system date alone. It does not include the time part.
  • current_timestamp(): This function returns the current system time and date in a complete format.
  • unix_timestamp(): This function uses the default time zone of the Unix epoch and returns a converted time format of the number of seconds between the Unix epoch and the specified date in string format.
  • unix_timestamp(str date): This function is used to convert  ‘yyyy-MM-dd HH:mm: ss’ date format into normal Unix timestamp. It returns a converted time format of the number of seconds between the Unix epoch and the specified date in string format. And, it returns 0 on failure.
  • unix_timestamp(str date, str pattern): This function is used to convert normal data type into another data type which can be specified in string pattern. It returns a converted time format of the number of seconds between the Unix epoch and the specified date in string format. And, it returns 0 on failure.
  • from_unixtime(bigint number of seconds  [, str format]): This function, from Unix epoch, converts the given number of seconds and returns the date in ‘yyyy-MM-dd HH:mm: ss’ format.
  • from_utc_timestamp(str date, time zone): This function converts the specified date format in string format to the specified time zone in the second part of the expression. This function is used for the time zone conversion of the UTC time zone to another time zone.
  • to_utc_timestamp(str date, time zone): This function converts the specified data format in a string format in the time zone specified in the second part to the UTC time zone. This function is used for the time zone conversion of another time zone to the UTC time zone.
  • to_date(str timestamp): This function is used to return only the date part of the specified timestamp in standard date format ‘yyyy-MM-dd’.
  • date_add(str date, int number of days): This function is used to add the specified number of days to the given date and return the final added date.
  • date_sub(str date, int number of days): This function is used to subtract the specified number of days from the given date and return the final subtracted date.
  • date_diff(str date 1, str date 2): This function is used to find the difference between two specified dates and returns the difference in the number of days.
  • Year (str date): This function is used to return the year portion of the given date in string format.
  • Quarter (str date): This function is used to return the year portion of the given date in string format.
  • Month (str date): This function is used to return the month portion of the given date in string format.
  • Day (str date): This function is used to return the day portion of the given date in string format.
  • dayofmonth (timestamp or date or string): This function is used to return the quarter number of the given date in string format.
  • Hour (str date): This function is used to return the given date’s hour portion in string format.
  • Minute (str date): This function is used to return the minute portion of the given date in string format.
  • Second (str date): This function is used to return the second portion of the given date in string format.
  • weekofyear(str date): This function is used to return the week number of the given date in string format.
  • trunc(timestamp, str unit): This function is used to strip off all the given timestamp fields in string format.
  • last_day(str date): This function is used to return the last day of the specified month in the given date in string format.
  • next_day(str start date, str day of the week): This function is used to return the next day of the week from the given start date.
  • months_between(str date 1, str date 2): This function returns the number of months between the given dates.
  • date_format(timestamp or date or string, str format): This function is used to convert the specified date in any specified to the given format.

How Does Date Function work in Hive?

  • Hive does not have date data types.
  • Dates in Hive are considered as normal strings.
  • Hive provides this wide variety of Date Functions for working, manipulating, and processing of date data types as per the requirements.
  • Date data type handling in Hive is more difficult as compared to RDBMS.
  • Hive is not able to throw an effective error when dealing with improper date formats.
  • Any format incompatible date formats will result in NULL as output instead of throwing an error in Hive.
  • date_format function can be used to convert any format to the required date format.

Examples to Implement in Date Functions

Below we will learn about the examples to implement in date function:

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,241 ratings)

1. current_date()

Code:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

select current_date();

Output:

Date Functions in Hive - 1

2. current_timestamp()

Code:

select current_timestamp();

Output:

Date Functions in Hive - 2

3. unix_timestamp(str date)

Code:

select unix_timestamp('2000-01-01 00:00:00');

Output:

Date Functions in Hive - 3

4. unix_timestamp(str date, str pattern)

Code:

select unix_timestamp('2009-03-20 00:00:00', 'yyyy-MM-dd');

Output:

Date Functions in Hive - 4

5. from_unixtime(bigint number of seconds  [, str format])

Code:

select from_unixtime(unix_timestamp());

Output:

Date Functions in Hive - 5

6. from_utc_timestamp(str date, time zone)

Code:

select from_utc_timestamp('1990-01-01 09:00:00', 'JST');

Output:

Date Functions in Hive - 6

7. to_utc_timestamp(str date, time zone)

Code:

select to_utc_timestamp ('1990-02-13 00:00:00','America/Denver');

Output:

Date Functions in Hive - 7

8. to_date(str timestamp)

Code:

select to_date ('1990-02-13 07:57:33');

Output:

Date Functions in Hive - 8

9. date_add(str date, int number of days)

Code:

select date_add ('2020-02-23', 4);

Output:

Date Functions in Hive - 9

10. date_sub(str date, int number of days)

Code:

select date_sub ('2020-02-23', 4);

Output:

Date Functions in Hive - 10

11. date_diff(str date 1, str date 2)

Code:

select date_add ('2020-02-23', 2020-02-19);

Output:

Date Functions in Hive - 11

12. year(str date)

Code:

select year ('2020-02-23 05:23:36');

Output:

year

13. quarter(str date)

Code:

select quarter ('2020-02-23 05:23:36');

Output:

quarter

14. month(str date)

Code:

select month ('2020-02-23 05:23:36');

Output:

month

15. day(str date)

Code:

Select day('2020-02-23 05:23:36');

Output:

day

16. dayofmonth(timestamp or date or string)

Code:

select dayofmonth ('2020-02-23 05:23:36');

Output:

dayofmonth

17. hour(str date)

Code:

select hour ('2020-02-23 05:23:36');

Output:

hour

18. minute(str date)

Code:

select minute ('2020-02-23 05:23:36');

Output:

minute

19. second(str date)

Code:

select second ('2020-02-23 05:23:36');

Output:

second

20. weekofyear(str date)

Code:

select weekofyear ('2020-01-23 05:23:36');

Output:

weekofyea

21. months_between(str date 1, str date 2)

Code:

select months_between ('2020-01-20’, ‘2019-10-15’);

Output:

 months_between

Recommended Articles

This is a guide to Date Functions in Hive. Here we discuss syntax, how does the date function in hive work with examples to implement with proper codes and outputs. You can also go through our other related articles to learn more –

  1. Views in Hive
  2. Bucketing in Hive
  3. Hive Data Types
  4. Hive String Functions
  5. Guide to PostgreSQL Date 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