I am trying to change the format of a timestamp in AWS Athena but I am not able to get it correct, would someone please help?
The value (Data format: string (Partitioned)) of the column I am trying to change is 20220826T073200Z and I would like the output to be 2022-08-26 07:32:00
You need to parse date first, for example with date_parse:
select date_parse('20220826T073200Z', '%Y%m%dT%H%i%sZ');
Output:
| _col0 |
|---|
| 2022-08-26 07:32:00.000 |
If this is not good enough you can format it with date_format:
select date_format(date_parse('20220826T073200Z', '%Y%m%dT%H%i%sZ'), '%Y-%m-%d %H:%i:%s');
| _col0 |
|---|
| 2022-08-26 07:32:00 |