How to add date to a file’s name using UNLOAD in Redshift

I found 2 solutions:

1. Using AWS Data Pipeline to schedule the query (Unload) and use ‘s3://reporting-team-bucket/importfiles/test_123-#{format(@scheduledStartTime,’YYYY-MM-dd-HH’)}.csv’
2. writing an MV command to rename the file on the s3 bucket

Is there a way to give a file’s the current date by only using Redshift, with no other services?

Here is my code so far:

unload
(
'select * from table'
)
to 's3://bucket/unload_test/test_123_{CurrentDate}.gz'
ACCESS_KEY_ID '12345678910'
SECRET_ACCESS_KEY '10987654321'
GZIP
PARALLEL off; 

Just need to get CurrentDate to be 202106 for example.

Thanks!

Answer

I’ve never tried using UNLOAD in a transaction, but if it works, you could use a procedure.