Parse date timestamp in SQL filename using RegEx

Question

I am building a bash script for backing up databases. I've already set up a cron job to running this script daily and I already can dump the .sql files according to the following format:

YYYYMMDD_HHMMSS-databasename.sql

Considering the timestamp formatted name, I want to build another bash script that will parse the YYMMDD filename part and select all the daily files of the last week. This new bash script will run weekly.

How can I parse these numbers into a date using regex?

Answer 1

Selecting the date part from a filename with RegEx:

^(20[12]\d)(0[1-9]|1[012])(0[1-9]|[12]\d|3[01])_\d+-\w+\.sql$

Explained regex here: http://regex101.com/r/iU7wL5

Update with correct time validation also:

^(20[12]\d)(0[1-9]|1[012])(0[1-9]|[12]\d|3[01])_([01]\d|2[0-3])[0-5]\d[0-5]\d-\w+\.sql$

Explained demo: http://regex101.com/r/yV1dD7

Note: this works on dates in 2010-2029 range and validates the filename to your output format

Answer 2

Here a complete solution, try doing this :

without regex but offset cut (assuming your example is the same format for all files , like a script does when running in crontab):

cd /path/to/dumps
str='20130321_145907-databasename.sql'
for i in {7..14}; do
    curfile=$(date -d ${str:0:8} -d "$i days ago" '+%Y%m%d')*
    if [[ -s $curfile ]]; then
        # do something with "$curfile"
    fi
done

If you really need a regex :

cd /path/to/dumps
str='20130321_145907-databasename.sql'
if [[ $str =~ ^([0-9]{8})_[0-9]{6} ]]; then
    for i in {7..14}; do
        curfile=$(date -d ${BASH_REMATCH[1]} -d "$i days ago" '+%Y%m%d')*
        if [[ -s $curfile ]]; then
            # do something with "$curfile"
        fi
    done
fi

Note

• note the final glob * on the curfile= line