Forum Discussion
Converting Epoch Date fields within the import statement
Hi all, I am using a native query to import data from my source table. The date are in Epoch format but the issue is that I do not get the minutes and seconds converted during the import. Below is my sql import statement:
SELECT c1000000348 as "Epoch StartDate",DATE('1970-01-01') + (c1000000348/86400) AS "Actual Start Date",DATE('1970-01-01') + (c1000000364/86400) AS "Actual End Date" FROM t3679 WHERE C6 >= '1640995200'
Result returned is:
As you will notice, 1689601478 is converted to "2023-07-17 12:00:00 AM". I am missing minutes and seconds for the Actual Start Date and the Actual End Date.
Pls assist
Thank you
Three points:
1. I'm no expert in SQL but depending on the version of SQL you are using in your database, it may be that division of two integers will result in an integer. If that is the case, you may be able to convert the numerator to a float by multiplying by 1.0
2. Your version of SQL may have builtin epoch converter you can use instead. If it is PostgreSQL look for the to_timestamp() function.
3. In power query M code, you could convert your `Epoch StartDate` to a real date using code similar to below
#"Add Start Date" = Table.AddColumn(#"Previous Step","Actual Start Date", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[Epoch StartDate]), type datetime)
5 Replies
Three points:
1. I'm no expert in SQL but depending on the version of SQL you are using in your database, it may be that division of two integers will result in an integer. If that is the case, you may be able to convert the numerator to a float by multiplying by 1.0
2. Your version of SQL may have builtin epoch converter you can use instead. If it is PostgreSQL look for the to_timestamp() function.
3. In power query M code, you could convert your `Epoch StartDate` to a real date using code similar to below
#"Add Start Date" = Table.AddColumn(#"Previous Step","Actual Start Date", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[Epoch StartDate]), type datetime)Good day, thanks for the assistance. I tried the proposed above using TO_Timestamnp function and got a fail. See below. The last option though requires that I first import the data filed, then convert wihtin Power Query.
Glad to hear that. And yes, the PQ option converts the unix time stamp into a datetime value if you can't do it in SQL
Hi on, thanks for the suggestion. It finally worked to give me the hh:mn values. Thanks very much
Hi ekodami
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.
