I am trying to export data from a select statement to a csv where the filename is made up in part by the date-time stamp.
eg export_2014-12-23-14-56 (export_yyyy_mm_dd_hh_mm) but I would take any format.
The code that currently generates the export is
cd c:\program files\postgresql\9.2\bin
psql -U postgres -h 10.0.0.69 -d mbs01 -f -t -A -F"," -c "select * from bespoke.fedex_export" > y:\warehouse\fedex\fedex_export2.txt -L e:\fedex_export.txt
This is really more of a windows question than a postgresql question as you're dealing with how to build a file name on the command line. You can use the %time% and %date%.
If you just call %date% for example, it will output a full string like:
>echo %date%
Tue 12/23/2014
So, you'll want to take substrings from that date to build your filename. The first being the year:
>echo %date:~-4,4%
2014
This starts at index -4 (4 characters from the end of the string) and gives you 4 characters of output.
You can also use positive indexes like so:
echo %date:~10,4% 2014
To generate your desired format, select just the substrings from %date% and %time% that you want like so:
psql -U postgres -h 10.0.0.69 -d mbs01 -f -t -A -F"," -c "select * from bespoke.fedex_export" > export_%date:~-4,4%_%date:~-7,2%_%date:~-10,2%_%time:~0,2%_%time:~3,2%.txt
