CODEWITHSUNDEEP
postgresqlpsql
Chris Nash
Chris Nash

Reputation: 33

Postgresql export to CSV with Date/Timestamp in file name

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

Upvotes: 0

Views: 4056

Answers (1)

Dave Koston
Dave Koston

Reputation: 304

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

Upvotes: 1

Related Questions