How to append datetime in filename in postgresql
If using psql via bash command line, you can simply do:

psql -c "COPY (select  query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"