We may get data from various sources which typically have various kinds of date formats. If we generate a hive table on top of these data, it will be essential to transform them into a date format that is supported by the hive.
Hive Date and Timestamp functions are used to control Date and Time on HiveQL inquiries over Hive CLI, Beeline, Spark, Java, Python, and many more applications. The default date format of Hive is yyyy-MM-dd, and for Timestamp yyyy-MM-dd HH: mm: ss.
When using Date and Timestamp in string formats, Hive assumes these are in default formats. If the format remains in a different format, you require to clearly define the input pattern to help Hive to understand and parse. Hive supports all formats defined in Java SimpleDateFormat.
Date types are extremely formatted and extremely complex. Each date value contains the century, year, month, day, hour, minute, and second. We shall see what are Hadoop Hive date functions. You can utilize these functions as Hive date conversion operates to manipulate the date data type according to the application requirements. The following section list the most commonly used Hadoop Hive DateTime functions.
1. current_date(): it returns the current system date alone. It does not include the time part.
2. current_timestamp(): it returns the current system time and date in a complete format.
3. unix_timestamp(): it 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.
4. unix_timestamp(str date): it is used to convert 'yyyy-MM-dd HH:mm: ss' date format into a 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.
5. unix_timestamp(str date, str pattern): it is used to convert a normal data type into another data type that can be specified in a 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.
6. from_unixtime(bigint number of seconds [, str format]): it is used to convert the given number of seconds and returns the date in 'yyyy-MM-dd HH:mm: ss' format.
7. from_utc_timestamp(str date, time zone): it 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.
8. to_utc_timestamp(str date, time zone): it 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.
9. to_date(str timestamp): it is used to return only the date part of the specified timestamp in standard date format 'yyyy-MM-dd'.
10. date_add(str date, int number of days): it is used to add the specified number of days to the given date and return the final added date.
11. date_sub(str date, int number of days): it is used to subtract the specified number of days from the given date and return the final subtracted date.
12. date_diff(str date 1, str date 2): it is used to find the difference between two specified dates and returns the difference in the number of days.
13. Year (str date): it is used to return the year portion of the given date in string format.
14. Quarter (str date): it is used to return the year portion of the given date in string format.
15. Month (str date): it is used to return the month portion of the given date in string format.
16. Day (str date): it is used to return the day portion of the given date in string format.
17. dayofmonth (timestamp or date or string): it is used to return the quarter number of the given date in string format.
18. Minute (str date): it is used to return the minute portion of the given date in string format.
19. Second (str date): it is used to return the second portion of the given date in string format.