Menu
Is there any way to format the epoch to timeformat 'YYYY-MM-DD HH:MI:SS' while doing redshift copy from s3 to redshift using COPY command
475
Epochs are by definition counted from 1970-01-01 00:00:00 in UTC so this is a little awkward. Fortunately, Redshift has some easy solutions for us.
A COPY command is the most efficient way to load a table. You can also add data to your tables using INSERT commands, though it is much less efficient than using COPY. The COPY command is able to read from multiple data files or multiple data streams simultaneously.
The default date format is YYYY-MM-DD. The default timestamp without time zone (TIMESTAMP) format is YYYY-MM-DD HH:MI:SS. The default timestamp with time zone (TIMESTAMPTZ) format is YYYY-MM-DD HH:MI:SSOF, where OF is the offset from UTC (for example, -8:00.
You can directly convert the format by specifying the cast operator ('::') such as ::date, ::time, ::timestamp after the date string literal. You can use the Redshift date format functions to convert the string literals, integer, date/time etc to required format.
You can use redshift COPY command with parameter TIMEFORMAT 'epochsecs'
or TIMEFORMAT 'epochmillisecs'
Check redshift documentation for more details
169
Sample COPY query using javascript miliseconds (13 digits): Possible options in documentation
COPY "hits" FROM 's3://your-bucket/your_folder/'
CREDENTIALS 'aws_access_key_id=<AWS_ACCESS_KEY_ID>;aws_secret_access_key=<AWS_SECRET_ACCESS_KEY>'
FORMAT as JSON 's3://your-bucket/config/jsonpaths'
TIMEFORMAT as 'epochmillisecs';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
