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

Question

I have JSON data stored in an S3 bucket, and I'm creating an Athena table to query this data. One of the columns in the JSON represents monetary values stored as integers (e.g., 1000 represents $10.00). I need to display these values in my Athena table with three decimal places (e.g., 1000 should be displayed as $10.000). Is there a way to achieve this directly in the DDL (Data Definition Language) when creating the Athena table, perhaps by dividing the integer values by 100? Any insights or examples would be greatly appreciated.

i tried it with decimal data type of athena like decimal(4,2) but it add .00 to the end of every integer

Answer

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.

```sql
    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](https://trino.io/docs/current/functions/logical.html#effect-of-null-on-logical-operators)
- [DECIMAL](https://trino.io/docs/current/functions/decimal.html#decimal-literals)

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

Relevant content