How to Display Decimal Values with 3 Decimal Places in Athena Table from Integer JSON Data Stored in S3?

You can use the CAST function in combination with division. Since functions in Athena engine version 3 are based on Trino, it supports casting data types.

💡 When querying the data, you can cast the integer values to a decimal type and divide by 100 to get the correct monetary value with three decimal places.

    SELECT id, CAST(amount AS DECIMAL(18, 3)) / 100.0 AS formatted_amount
    FROM your_table;

In this query, amount is the column containing the integer monetary values. By casting it to DECIMAL(18, 3), you're specifying a decimal type with 18 digits of precision and 3 decimal places. Dividing by 100.0 converts the integer values to their correct decimal representation (e.g., 1000 becomes 10.000).

Key Sources:

• CAST
• DECIMAL