Automatically alter the “date added” for a video file in the Synology Video Station database, to match the last modification date of the file on disk

set_video_metadata_date_created.sh

#!/bin/sh

set -e

if [ -z "$1" ]; then
    cat <<'EOF'

Usage:
  ./set_video_metadata_date_created.sh '/absolute/path/to/video/file.mp4'

Example, looping over all video files in a directory:
  find /absolute/path/to/video/files -type f \( -name '*.avi' -o -name '*.mov' -o -name '*.mkv' -o -name '*.mp4' -o -name '*.m4v' \) -exec ./set_video_metadata_date_created.sh {} \;

EOF
    exit 2
fi

created=$(date -r "$1" -u '+%Y-%m-%d %H:%M:%S')

echo "$1"
echo "  created: $created"

path_escaped=$(echo $1 | sed "s/'/''/g")

info=$(psql -X -A -U postgres -d video_metadata -t -c "select mapper.id, mapper.type from video_file, mapper where video_file.mapper_id = mapper.id and video_file.path = '$path_escaped';")

if [ ! -z $info '' ]; then

    mapper_id=$(echo $info | awk -F '|' '{print $1}')
    mapper_type=$(echo $info | awk -F '|' '{print $2}')

    echo "  mapper_id: $mapper_id"
    echo "  mapper_type: $mapper_type"

    psql -U postgres -d video_metadata -q -c "UPDATE video_file SET create_date = '$created' WHERE mapper_id = $mapper_id;"

    psql -U postgres -d video_metadata -q -c "UPDATE $mapper_type SET create_date = '$created' WHERE mapper_id = $mapper_id;"

fi

here is a fixed version

#!/bin/sh

set -e

if [ -z "$1" ]; then
echo "Usage:
./set_video_metadata_date_created.sh '/absolute/path/to/video/file.mp4'
Example, looping over all video files in a directory:
find /absolute/path/to/video/files -type f ( -name '.avi' -o -name '.mov' -o -name '.mkv' -o -name '.mp4' -o -name '*.m4v' ) -exec ./set_video_metadata_date_created.sh {} ;"
exit 2
fi

        created=$(date -r "$1" -u '+%Y-%m-%d %H:%M:%S')

        echo "$1"
        echo "  created: $created"

        path_escaped=$(echo $1 | sed "s/'/''/g")

        info=$(psql -X -A -U postgres -d video_metadata -t -c "select mapper.id, mapper.type from video_file, mapper where video_file.mapper_id = mapper.id and video_file.path = '$path_escaped';")

        #if [ ! -z $info '' ]; then
        if [ ! "$info" == "" ]; then

            mapper_id=$(echo $info | awk -F '|' '{print $1}')

                mapper_type=$(echo $info | awk -F '|' '{print $2}')

                    echo "  mapper_id: $mapper_id"
                        echo "  mapper_type: $mapper_type"

                            psql -U postgres -d video_metadata -q -c "UPDATE video_file SET create_date = '$created' WHERE mapper_id = $mapper_id;"

                                psql -U postgres -d video_metadata -q -c "UPDATE $mapper_type SET create_date = '$created' WHERE mapper_id = $mapper_id;"

                                fi