Sqlite - Unix Epoch Millis

[QST]

Hello  All,

I'm having an issue regarding the import of data. 

I have an SQLite Database file that has one table. That table is comprised of Types INTEGER and DOUBLE. My issue is that I'm storing Unix Epoch as INTEGER NOT NULL with 13 characters (for example Value in that column - 1511240400207  which is GMT: Tuesday, November 21, 2017, 5:00:00.207 AM ( @ epochconverter.com) but in PowerBI, that specific column is 2147483647 with 10 characters. 

I have installed both 32/64 bit driver of  SQLite ODBC @ http://www.ch-werner.de/sqliteodbc/

All other columns are correct. 

Could someone please point me in the right direction?

Thank You

Kind Regards

QST

[v-yulgu-msft]

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

[QST · Accepted solution]

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