Truncating Date in Apache Impala

There are some built-in functions that always assist analysts and developers in making their work easier. Undoubtedly The ‘DATE TRUNC’ function is one of them. This function will aid in the truncation of the “Date” and “TimeStamp.”

Although calling TRUNC() with a TIMESTAMP or DATE argument is similar, the order of arguments and recognized units differ between TRUNC() and DATE TRUNC (). As a result, these functions are interchangeable. In GROUP BY queries, this function is typically used to aggregate results from the same hour, day, week, month, quarter, and so on. This function can also be used in an INSERT… SELECT into a partitioned table to divide TIMESTAMP or DATE values into the appropriate partition.

Please keep in mind that the unit argument is case-insensitive.

Examples:
SELECT DATE_TRUNC(‘SECOND’, NOW());
SELECT DATE_TRUNC(‘MINUTE’, NOW());
SELECT DATE_TRUNC(‘HOUR’, NOW());
SELECT DATE_TRUNC(‘DAY’, DATE’2019-08-02′);
SELECT DATE_TRUNC(‘WEEK’, NOW());
SELECT DATE_TRUNC(‘MONTH’, DATE’2019-08-02′);
SELECT DATE_TRUNC(‘YEAR’, NOW());
SELECT DATE_TRUNC(‘DECADE’, DATE’2019-08-02′);
SELECT DATE_TRUNC(‘CENTURY’, NOW());
SELECT DATE_TRUNC(‘MILLENNIUM’, DATE’2019-08-02′);
SELECT DATE_TRUNC(‘DAY’, DATE’2019-08-02′);
SELECT DATE_TRUNC(‘WEEK’, DATE’2019-08-02′);
SELECT DATE_TRUNC(‘MONTH’, DATE’2019-08-02′);

Please note that this function is added from version 2.11 hence you’ll not be able to find it in free versions of CDH VMs.

Hope you find this article helpful.

Please subscribe for more interesting updates.