Converting Unix epoch time to extended ISO8601

Question

I have 3 tables I would like to work on using the date, however one of the tables includes the date in unix epoch format. Here is an example of the 3 fields:

Table1: 2017-02-01T07:58:40.756031Z
Table2: 2017-02-07T10:16:46Z
Table3: 1489236559

I would like to convert the date in table 3 to match the format of table 2 as closely as possible. This is what I have right now:

SELECT cast(from_unixtime(tstart) as timestamp) as ISODATE from scada_logs

This gives me something tantalizingly close, but not quite there. Here is what I get:

ISODATE
2017-03-20 14:31:06.000
2017-03-20 14:31:06.000

I've played around with this for a couple of hours but I'm not getting any closer. Does anyone have any ideas?

Thank you!

Answer 1

Option 1: date_format

presto> select date_format(from_unixtime(1489236559),'%Y-%m-%dT%H:%i:%sZ');
        _col0
----------------------
 2017-03-11T12:49:19Z

Option 2: to_iso8601

presto> select to_iso8601(from_unixtime(1489236559));
          _col0
--------------------------
 2017-03-11T12:49:19.000Z