Question 401136 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – AWS Athena SQL Query – Converting date to String

I have a Athena SQL Query where I am calculating some delivery date like below.

CASE WHEN eventtime IS NOT NULL
    AND eventtime != '' THEN
    date_format(from_iso8601_timestamp (eventtime), '%Y-%m-%d %H:%i:%s')
ELSE
    NULL
END AS computation_date

When I download the data as CSV and try to open an Excel ], I cannot see the seconds values in the Excel (due to some default format). To fix this, I am trying to concatenate the ‘ string to the date value so that the CSV data will consider this as a string. I tried with array agg and array join, but it’s not working properly for me.

example:
Sample data 2024-06-04 12:39:00 , 2024-06-04 12:11:00 this is the output data in AWS Athena SQL
in Excel i can able to see 2024-06-04 12:39 ,2024-06-04 12:11 .
trying to add string "’" to the value ‘2024-06-04 12:39:00 , ‘2024-06-04 12:11:00

Tags:

2

Answers


  1. 0

    trying to add string "’"

    If the goal is to add ' character to the output you can use another ' to escape it in the output format:

    select date_format(now(), '''%Y-%m-%d %H:%i:%s''');

    Output:

             _col0
-----------------------
 '2024-08-07 10:25:50'
    Login or Signup to reply.
  2. 0

    There is no need to do that in the code. The problem is with Excel and the default way it’s showing values. To fix that and to see the seconds, you should:

    • Select cells that contain the values
    • Right click -> format cells
    • On Number tab select Category->Custom
    • In Type enter yyyy-mm-dd H:mm:ss

    This will tell Excel to show you date formatted the way you want, including seconds

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search