Menu
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!
365
Convert from epoch to human-readable datemyString := DateTimeToStr(UnixToDateTime(Epoch)); Where Epoch is a signed integer. Replace 1526357743 with epoch. =(A1 / 86400) + 25569 Format the result cell for date/time, the result will be in GMT time (A1 is the cell with the epoch number).
How is it more accurate? ISO-8601 allows for leap seconds, it uses the same second counting frequency as the Unix timestamp, it can represent dates before 1970 and after 2038 (64-bit Unix timestamps will also.) and it's an international standard.
If you have a list of timestamp needed to convert to date, you can do as below steps: 1. In a blank cell next to your timestamp list and type this formula =(((A1/60)/60)/24)+DATE(1970,1,1), press Enter key, then drag the auto fill handle to a range you need.
presto> select date_format(from_unixtime(1489236559),'%Y-%m-%dT%H:%i:%sZ');
_col0
----------------------
2017-03-11T12:49:19Z
presto> select to_iso8601(from_unixtime(1489236559));
_col0
--------------------------
2017-03-11T12:49:19.000Z
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
