cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nghtwlker17
Helper I
Helper I

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

 

nghtwlker17_0-1632839353760.png

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

 

1 ACCEPTED SOLUTION

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.

View solution in original post

5 REPLIES 5
ronrsnfld
Responsive Resident
Responsive Resident

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.

View solution in original post

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!

Here is a formula for a Custom Column that applies your algorithm:

 

let 
          str = Number.ToText([Column1],"0000000000"),
          dtPart = Text.Start(str,5),
          sec = Number.From(Text.End(str,5))
        in 
          DateTime.From(Number.From(dtPart) - 36522 + sec/86400)

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors