Forum Discussion
Problem with Unix timestamp to Date
I did some additional tinkering, and found that the first 5 digits represent a date # that when converted to date and then minus 36522 days, provides the correct date. The second 5 numbers represented a number of seconds. I had to split them, and do multiple levels of custom columns to manipulate them to a correct date/time combined value.
I'll chalk this up to creative coders for Allscripts Horizon software that is nearly 15 years old. I don't have any other explanation.
Those numbers do not appear to be Unix epoch timestamps representing the dates you think they are. The unix time for 27-Jul-2019 21:08 is 1564261680. Not sure how that relates to 8019558080.
I did some additional tinkering, and found that the first 5 digits represent a date # that when converted to date and then minus 36522 days, provides the correct date. The second 5 numbers represented a number of seconds. I had to split them, and do multiple levels of custom columns to manipulate them to a correct date/time combined value.
I'll chalk this up to creative coders for Allscripts Horizon software that is nearly 15 years old. I don't have any other explanation.
Glad you found a solution.
When I apply your algorithm, however, I wind up with a dateTime that is off by 5 hours (which, coincidentally, is the time zone difference between my locale Standard time (EST) and UTC.
I don't know if it is any more efficient, but you could combine the steps into a single column formula.
That would probably be more efficient and a lot more elegant. The setup I currently have has roughly six steps for each column in order to end with a date/time column, and the users may ask to apply it to other timestamps in that table.
I'll look into making this a single calculation using VAR / RETURN, thank you for the suggestion!