How to define timestamp without mls in Athena table

Hello Sara,

In Athena, when you're dealing with timestamp data in Parquet files that don't include milliseconds (ms), you can still read and convert these columns into timestamp data types. However, you need to handle the timestamp format during the query, as Parquet itself does not store timestamp formats.

You can use the CAST and FROM_ISO8601_TIMESTAMP functions to convert the varchar timestamp data into a timestamp data type during your query. Here's how you can do it:

Assuming you have a table with the column timestamp_column containing varchar timestamp data ('2023-08-09 15:41:42'), you can run a query like this to convert it to a timestamp:

SELECT
  CAST(FROM_ISO8601_TIMESTAMP(timestamp_column) AS timestamp) AS timestamp_column
FROM
  your_table_name;

This query does the following:

1. FROM_ISO8601_TIMESTAMP(timestamp_column) converts the varchar timestamp to a timestamp with milliseconds.
2. CAST(... AS timestamp) casts the result back to a timestamp without milliseconds.

Replace your_table_name with the actual name of your Athena table.

This way, you can read and work with your timestamp data as timestamps without milliseconds directly in your queries without the need to modify the table's schema or format settings.

Please give a thumbs up if this suggestion helps