Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Epoch date format to mm-dd-yyyy hh:mm:ss format

I have imported data from mysql database and the time column in in the format of epoch(1546891681) and want to convert into readable date and time format. 

 

 

 

 

 

1 ACCEPTED SOLUTION

FYI - you can do this in calculated columns as above, but you said @Anonymous you were getting this from a MySQL database. That means it should be going through Power Query. Calculated columns should be a last resort for performance reasons. This SQLBI blog walks you through some of that. I always try to go with Power Query transformations before considering calculated columns in DAX.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
Asac_14
Resolver I
Resolver I

Create the following calculated columns and add them to get the desired format

 

Days='Quotient('Table'[epoch],86400)

Hours = Quotient(MOD('Table'[epoch],86400),3600)

Mins = Quotient(MOD('Table'[epoch],3600),60)

Sec = MOD(MOD('Table'[epoch],3600),60)

 

Final Column = Date(1970,01,01) + Days + Hours + Mins + Sec

07 Jan 2019 8:08:01 PM is the values for the sample number given by you. @Anonymous  If this helps you kindly accept this as solution and give a kudos 😉

IMG_20191027_201213.jpg

FYI - you can do this in calculated columns as above, but you said @Anonymous you were getting this from a MySQL database. That means it should be going through Power Query. Calculated columns should be a last resort for performance reasons. This SQLBI blog walks you through some of that. I always try to go with Power Query transformations before considering calculated columns in DAX.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Use the #datetime() and #duration() functions in Power Query. Epoch time is the number of seconds from Jan 1, 1970.

 

Type = #datetime(1970,1,1,0,0,0) + #duration(0,0,0,1546891690) in a new blank query, then convert to a table, then change the data type to DateTime. A full example below.

 

You can do the same in a custom column by changing the above formula to ... + duration(0,0,0,[epochtimecolumn])

 

let
    Source = #datetime(1970,1,1,0,0,0) + #duration(0,0,0,1546891690),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}})
in
    #"Changed Type"

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.