Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
new to the forum and new to data modelling...
I am connecting to a PostGres Database of our ticketing system (ManageEngine ServiceDesk) where timestamps are stored in what i assume to be a java long date format.
The field CREATEDTIME (when a ticket was created) has a value e.g. 1530272032346 which equates to Friday, 29 June 2018 11:33:52 o'clock UTC according to the following website http://www.fileformat.info/tip/java/date2millis.htm
When I try to convert the number using the data type selector in the query editor, it seems that the system cannot interpret this number as a date and returns errors.
Only if the value is 0 (e.g. in a RESPONDEDTIME field on a ticket where no response was logged yet) it returns 30/12/1899 00:00:00 +01:00
Tried all sorts of settings with regional settings, locale settings etc and have not managed to convert.
Can anybody give me a hint as to what i need to do?
Kind regards
Albert
Solved! Go to Solution.
hi,@AlbertMaes
You can add a calculate column when you modeling like this
Date = DATEVALUE("1970-1-1") + (Table1[Column1]/(60*60*24*1000))
Result:
Best Regards,
Lin
hi,@AlbertMaes
You can add a calculate column when you modeling like this
Date = DATEVALUE("1970-1-1") + (Table1[Column1]/(60*60*24*1000))
Result:
Best Regards,
Lin
Thank you Lin! Works a treat!!
I did some more digging here and it seems that indeed my database churns out Java Date Stamps (Day 0 = 01/01/1970), PowerBI seems to expect SQL Date Stamps (Day 0 = 12/12/1899)
How do I tell PowerBI to use a different date stamp?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
72 | |
68 | |
45 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |