I am using SQL in ATHENA and i have timestamps like this: 1634009513663. I guess it's UNIX up to milliseconds? I am trying to convert it into datetime format but I run into an error. I guess it's the string format I've passed:

SELECT date_parse(to_iso8601(from_unixtime(cast(timestamp as double))),'%Y-%m-%dT%H:%M:%S.%f')

INVALID_FUNCTION_ARGUMENT: Invalid format: "53736-04-13T06:40:59.000Z" is malformed at "6-04-13T06:40:59.000Z"

CodePudding user response：

To parse unix timestamp to millisecond you can handle milliseconds manually:

WITH dataset(udt) AS (
    values (1634009513663)
)

SELECT date_add('millisecond', udt % 1000, from_unixtime(udt/1000))
FROM dataset

Output: