Reply
Highlighted
Expert Contributor
Posts: 69
Registered: ‎11-24-2017

Drop partition dynamically using date functions

[ Edited ]

 

I need to delete partitions dynamically performing a date computation.

The partitions are in the form: 

 

nominal_time=201804170630 (yyyyMMddHHmm)

 

 

For example to drop the partition of 3 days before I have tried this:

 

-- 259200 is the number of seconds of 3 days

SELECT from_unixtime(unix_timestamp
("201804170630", "yyyyMMddHHmm") - 259200), "yyyyMMddHHmm")

-- returns --> 201804140630

 

 

 

-- 259200 is the number of seconds of 3 days

ALTER TABLE mytable DROP IF EXISTS PARTITION(nominal_time=from_unixtime(unix_timestamp
("201804170630", "yyyyMMddHHmm") - 259200), "yyyyMMddHHmm"));

 

But this fails. How do you suggest to perform this task? 

 

 

 

 

Announcements