Forum Discussion
Problem with Unix timestamp to Date
Hello,
I have what looks like a datetimestamp that I am getting from a Oracle 11.2 database that I need to convert to standard date/time. When I looked this up, I found some of the below solutions, which unfortunately are not working for me:
Custom =
#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ARRIVAL_DDT])
I get the below
For this particular value, I have a UTC column that has the date/time stamp formatted correctly for comparison. Unfortunately, some of the columns I need to convert do not have this value, so I'm trying to figure out what I'm doing incorrectly on this conversion.
Appreciate any tips or advice on this one, please let me know if I can help with any additional details.
Regards,
Seth
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.
5 Replies
ronrsnfldSuper User
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.
- ronrsnfld
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.