.::: Shell Script to Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB some database, table :::.

correlation https://teguhth.blogspot.com/2025/11/grouping-year-month-day-time-date-for.html

1. run check all hardcode
 

2. run check manual 
 

3. script check hardcode

[root@teguhth-all countx]# cat table_count.sh
hostx=$(hostname -s)
ipx='10.10.10.90'
portx='3306'
userdb='admin'
pwddb='xxx'
dbname='teguhth'
tblname='datefilter'
cldatex='datex'
 
alias mysql=mariadb

echo "host   : $hostx"
echo "ip     : $ipx"
echo "port   : $portx"
echo "userdb : $userdb"
echo "dbname : $dbname"
echo "table  : $tblname"
echo "column : cldatex"

echo "--count all"
echo "select '${tblname}' as tablex,count(*) from $dbname.$tblname;"
echo
echo "--count group years"
echo "select '${tblname}' as tablex,year(${cldatex}) as year,count(*) as record_count from $dbname.$tblname group by year(${cldatex});"
echo
echo "--count group months"
echo "select '${tblname}' as tablex,date_format(${cldatex}, '%Y-%m') as month,count(*) as record_count from $dbname.$tblname group by date_format(${cldatex}, '%Y-%m') order by month;"
echo
echo "--count group days"
echo "select '${tblname}' as tablex,cast(${cldatex} as date) as day,count(*) as record_count from $dbname.$tblname group by cast(${cldatex} as date) order by day;"
echo

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,count(*) from $dbname.$tblname;"

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,year(${cldatex}) as year,count(*) as record_count from $dbname.$tblname group by year(${cldatex});"

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,date_format(${cldatex}, '%Y-%m') as month,count(*) as record_count from $dbname.$tblname group by date_format(${cldatex}, '%Y-%m') order by month;"

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,cast(${cldatex} as date) as day,count(*) as record_count from $dbname.$tblname group by cast(${cldatex} as date) order by day;"
[root@teguhth-all countx]#

4. script check manual

[root@teguhth-all countx]# pwd
/data/countx
[root@teguhth-all countx]#
[root@teguhth-all countx]# cat table_count_manual.sh

hostx=$(hostname -s)
ipx=${1}
portx=${2}
userdb='admin'
pwddb='xxx'
dbname=${3}
tblname=${4}
cldatex=${5}

echo "Example: sh $0 10.10.10.9 3306 teguhth datefilter datex"

alias mysql=mariadb

echo "host   : $hostx"
echo "ip     : $ipx"
echo "port   : $portx"
echo "userdb : $userdb"
echo "dbname : $dbname"
echo "table  : $tblname"
echo "column : cldatex"

echo "--count all"
echo "select '${tblname}' as tablex,count(*) from $dbname.$tblname;"
echo
echo "--count group years"
echo "select '${tblname}' as tablex,year(${cldatex}) as year,count(*) as record_count from $dbname.$tblname group by year(${cldatex});"
echo
echo "--count group months"
echo "select '${tblname}' as tablex,date_format(${cldatex}, '%Y-%m') as month,count(*) as record_count from $dbname.$tblname group by date_format(${cldatex}, '%Y-%m') order by month;"
echo
echo "--count group days"
echo "select '${tblname}' as tablex,cast(${cldatex} as date) as day,count(*) as record_count from $dbname.$tblname group by cast(${cldatex} as date) order by day;"
echo

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,count(*) from $dbname.$tblname;"

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,year(${cldatex}) as year,count(*) as record_count from $dbname.$tblname group by year(${cldatex});"

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,date_format(${cldatex}, '%Y-%m') as month,count(*) as record_count from $dbname.$tblname group by date_format(${cldatex}, '%Y-%m') order by month;"

#mysql -h$ipx -u$userdb -p$pwddb -P$portx -e "select '${tblname}' as tablex,cast(${cldatex} as date) as day,count(*) as record_count from $dbname.$tblname group by cast(${cldatex} as date) order by day;"
[root@teguhth-all countx]#

 

5. just command only

[root@teguhth-all countx]# pwd
/data/countx
[root@teguhth-all countx]# cat just_command_count.sh

dbname=${1}
tblname=${2}
cldatex=${3}

echo "Example: sh $0 teguhth datefilter datex"

alias mysql=mariadb

echo "host   : $hostx"
echo "ip     : $ipx"
echo "port   : $portx"
echo "userdb : $userdb"
echo "dbname : $dbname"
echo "table  : $tblname"
echo "column : cldatex"

echo "--count all"
echo "select '${tblname}' as tablex,count(*) from $dbname.$tblname;"
echo
echo "--count group years"
echo "select '${tblname}' as tablex,year(${cldatex}) as year,count(*) as record_count from $dbname.$tblname group by year(${cldatex});"
echo
echo "--count group months"
echo "select '${tblname}' as tablex,date_format(${cldatex}, '%Y-%m') as month,count(*) as record_count from $dbname.$tblname group by date_format(${cldatex}, '%Y-%m') order by month;"
echo
echo "--count group days"
echo "select '${tblname}' as tablex,cast(${cldatex} as date) as day,count(*) as record_count from $dbname.$tblname group by cast(${cldatex} as date) order by day;"
echo

[root@teguhth-all countx]#