I have a table in redshift which contains several columns with datetime datatype.
An example dataset would look like this:
| integer | timestamp | timestamp22 |
_____________________________________
| 1 | 2023-11-24 12:30:35.123 | 2023-11-24 12:30:25.000 |
| 2 | 2023-11-24 12:30:35.120 | 2023-11-24 12:30:25.200 |
I am trying to unload this data from redshift to s3 using the UNLOAD command in redshift. The query that i am using looks like this:
UNLOAD ('SELECT * FROM (SELECT "integer", "timestamp", "timestamp22" FROM "test_db"."test_table")') TO 's3://bucket/location' ENCRYPTED ACCESS_KEY_ID '<redacted>' SECRET_ACCESS_KEY '<redacted>' ESCAPE ADDQUOTES MANIFEST DELIMITER AS ','
After the unload, a couple of files get created at the s3 location since parallel unload is on by default and the data is written to them.
The data in s3 upon aggregation looks like this:
"1", "2023-11-24 12:30:35.123", "2023-11-24 12:30:25"
"2", "2023-11-24 12:30:35.12", "2023-11-24 12:30:25.2"
It could be noted that even though the actual data in redshift has millisecond precision to 3 places, but after unload the trailing zeroes get truncated at the s3 destination. I want to retain the precision in the destination as well. How could i modify the unload query or any way to circumvent this issue?
There's no actual loss of precision if you lose the trailing zeros in the S3 files. But if you want to keep them, you need to UNLOAD with a TO_CHAR that has the millisecond time formatter MS, like this:
UNLOAD ('SELECT TO_CHAR(timestamp22, ''YYYY-MM-DD HH24:MI:SS.MS'') FROM "test_db"."test_table"') ...