Script to create Weekly partition based on Unix Time Stamp in mysql

mysql_alarm_script.sh

#!/bin/bash

for i in {1..52}

do

year=$(date +"%Y" -d "2017-01-01")

date=$(date +"%Y/%m/%d" -d "2017/01/02 $i week") # Provide the first day of week started

unixtimestamp=$(date -d $date +"%s")

if [[ $i == 1 ]];then

echo "ALTER TABLE monitor.alarm_log ADD PARTITION (

PARTITION P_"$year"_"$i" VALUES LESS THAN ($unixtimestamp),"

elif [[ $i == 52 ]];then

echo "PARTITION P_"$year"_"$i" VALUES LESS THAN ($unixtimestamp));"

else

echo "PARTITION P_"$year"_"$i" VALUES LESS THAN ($unixtimestamp),"

fi

done

[munna@db]$ ./mysql_alarm_script.sh

ALTER TABLE monitor.alarm_log ADD PARTITION (

PARTITION P_2017_1 VALUES LESS THAN (1483916400),

PARTITION P_2017_2 VALUES LESS THAN (1484521200),

PARTITION P_2017_3 VALUES LESS THAN (1485126000),

PARTITION P_2017_4 VALUES LESS THAN (1485730800),

PARTITION P_2017_5 VALUES LESS THAN (1486335600),

PARTITION P_2017_6 VALUES LESS THAN (1486940400),

PARTITION P_2017_7 VALUES LESS THAN (1487545200),

PARTITION P_2017_8 VALUES LESS THAN (1488150000),

PARTITION P_2017_9 VALUES LESS THAN (1488754800),

PARTITION P_2017_10 VALUES LESS THAN (1489359600),

PARTITION P_2017_11 VALUES LESS THAN (1489964400),

PARTITION P_2017_12 VALUES LESS THAN (1490565600),

PARTITION P_2017_13 VALUES LESS THAN (1491170400),

PARTITION P_2017_14 VALUES LESS THAN (1491775200),

PARTITION P_2017_15 VALUES LESS THAN (1492380000),

PARTITION P_2017_16 VALUES LESS THAN (1492984800),

PARTITION P_2017_17 VALUES LESS THAN (1493589600),

PARTITION P_2017_18 VALUES LESS THAN (1494194400),

PARTITION P_2017_19 VALUES LESS THAN (1494799200),

PARTITION P_2017_20 VALUES LESS THAN (1495404000),

PARTITION P_2017_21 VALUES LESS THAN (1496008800),

PARTITION P_2017_22 VALUES LESS THAN (1496613600),

PARTITION P_2017_23 VALUES LESS THAN (1497218400),

PARTITION P_2017_24 VALUES LESS THAN (1497823200),

PARTITION P_2017_25 VALUES LESS THAN (1498428000),

PARTITION P_2017_26 VALUES LESS THAN (1499032800),

PARTITION P_2017_27 VALUES LESS THAN (1499637600),

PARTITION P_2017_28 VALUES LESS THAN (1500242400),

PARTITION P_2017_29 VALUES LESS THAN (1500847200),

PARTITION P_2017_30 VALUES LESS THAN (1501452000),

PARTITION P_2017_31 VALUES LESS THAN (1502056800),

PARTITION P_2017_32 VALUES LESS THAN (1502661600),

PARTITION P_2017_33 VALUES LESS THAN (1503266400),

PARTITION P_2017_34 VALUES LESS THAN (1503871200),

PARTITION P_2017_35 VALUES LESS THAN (1504476000),

PARTITION P_2017_36 VALUES LESS THAN (1505080800),

PARTITION P_2017_37 VALUES LESS THAN (1505685600),

PARTITION P_2017_38 VALUES LESS THAN (1506290400),

PARTITION P_2017_39 VALUES LESS THAN (1506895200),

PARTITION P_2017_40 VALUES LESS THAN (1507500000),

PARTITION P_2017_41 VALUES LESS THAN (1508104800),

PARTITION P_2017_42 VALUES LESS THAN (1508709600),

PARTITION P_2017_43 VALUES LESS THAN (1509318000),

PARTITION P_2017_44 VALUES LESS THAN (1509922800),

PARTITION P_2017_45 VALUES LESS THAN (1510527600),

PARTITION P_2017_46 VALUES LESS THAN (1511132400),

PARTITION P_2017_47 VALUES LESS THAN (1511737200),

PARTITION P_2017_48 VALUES LESS THAN (1512342000),

PARTITION P_2017_49 VALUES LESS THAN (1512946800),

PARTITION P_2017_50 VALUES LESS THAN (1513551600),

PARTITION P_2017_51 VALUES LESS THAN (1514156400),

PARTITION P_2017_52 VALUES LESS THAN (1514761200));