Hive provides the trunc() function for date manipulation. The trunc() function truncates a date to a specified unit of time, such as day, month, or year, by setting all lower-order units to zero.
The syntax of the trunc() function is as follows:
trunc(date, unit)
Here, date is the input date, and unit is the unit of time to truncate to. The unit argument can be one of the following strings: ‘YY’, ‘MM’, ‘DD’, ‘HH’, ‘MI’, ‘SS’, ‘J’, ‘DAY’, ‘MONTH’, ‘YEAR’, or ‘QUARTER’.
For example, to truncate the date ‘2022-03-05’ to the nearest month, you can use the following query:
SELECT trunc(‘2022-03-05’, ‘MM’);
This will return the date ‘2022-03-01’, which is the first day of the month.
Similarly, to truncate the same date to the nearest year, you can use the following query:
SELECT trunc(‘2022-03-05’, ‘YEAR’);
This will return the date ‘2022-01-01’, which is the first day of the year.
You can also use the trunc() function with timestamps to truncate to a specific unit of time, such as hour or minute.
For example, to truncate the timestamp ‘2022-03-05 15:30:00’ to the nearest hour, you can use the following query:
SELECT trunc(‘2022-03-05 15:30:00’, ‘HH’);
This will return the timestamp ‘2022-03-05 15:00:00’, which is the top of the hour.
Note that the trunc() function is different from the date_format() function, which formats a date or timestamp as a string according to a specified format string. The trunc() function returns a date or timestamp, not a string.