Forum Discussion
Sqlite - Unix Epoch Millis
Firstly, thank you for your kind and timely answer.
So I was trying to understand and fiddled around a bit.
1.Since PowerBI imports a strange number apriori.
Idea was to change the way it imports. So I messed around in SQLite Studio trying to convert the Unix epoch.
Arrived at :
SELECT datetime(Unixepochmillies/1000,'unixepoch') from table1;
and
SELECT datetime(Unixepochmillies/1000,'unixepoch','localtime') from table1;
Seems that maybe I have no option to include the milliseconds in this query leaving me with the seconds.
The result was that I got the same values for the first query without localtime. Giving me 23.00.00 correct without the Timezone compensation. Changing to Date/Time gave me exactly the same values and converting this query to Date/Time/TimeZone gave 23.00.00 +7.
Seems here that PowerBI just adds what my timezone is (which in fact is UTC+7). But one would assume it would calculate the new time in my timezone including the +7 hours (6 am the next day) .
On the second query, I found a discrepancy between running the command in SQLite Studio and PowerBI: in SQLite Studio the output was 18h of the previous day and on PowerBi it imported raw as 6 am assuming the difference apriori wich makes this query correct now. SQLite Studio here shows a quite different calculation since 23h UTC to a previous 18h is 5hours and not 7hours.
Apart from losing the milliseconds in this query, I guess that is working now.
Thank you again v-yulgu-msft for pointing in the right direction.
QST
Hi QST,
Please check whether the timezone of SQLite and Power BI desktop matches. In Query Editor, please change a date type column to "Date/Time/Timezone" to see relative timezone.
Regards,
Yuliana Gu
Firstly, thank you for your kind and timely answer.
So I was trying to understand and fiddled around a bit.
1.Since PowerBI imports a strange number apriori.
Idea was to change the way it imports. So I messed around in SQLite Studio trying to convert the Unix epoch.
Arrived at :
SELECT datetime(Unixepochmillies/1000,'unixepoch') from table1;
and
SELECT datetime(Unixepochmillies/1000,'unixepoch','localtime') from table1;
Seems that maybe I have no option to include the milliseconds in this query leaving me with the seconds.
The result was that I got the same values for the first query without localtime. Giving me 23.00.00 correct without the Timezone compensation. Changing to Date/Time gave me exactly the same values and converting this query to Date/Time/TimeZone gave 23.00.00 +7.
Seems here that PowerBI just adds what my timezone is (which in fact is UTC+7). But one would assume it would calculate the new time in my timezone including the +7 hours (6 am the next day) .
On the second query, I found a discrepancy between running the command in SQLite Studio and PowerBI: in SQLite Studio the output was 18h of the previous day and on PowerBi it imported raw as 6 am assuming the difference apriori wich makes this query correct now. SQLite Studio here shows a quite different calculation since 23h UTC to a previous 18h is 5hours and not 7hours.
Apart from losing the milliseconds in this query, I guess that is working now.
Thank you again v-yulgu-msft for pointing in the right direction.
QST