Epoch to timeformat 'YYYY-MM-DD HH:MI:SS'

Question

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

Answer 1

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';

Answer 2

You can use redshift COPY command with parameter TIMEFORMAT 'epochsecs' or TIMEFORMAT 'epochmillisecs' Check redshift documentation for more details