Hey all, hopefully this is the right place to post this question since I have no idea if its a lack of my own knowledge, or SQLite.jl, SQLite, or encodings in general. I did some work a while ago in Julia, and used the SQLite.jl package to save my results, one of the columns storing a DateTime object. I’m now trying to use this SQLite database with Python, but I can’t seem to decode the datetime part. This is what python sees: b'7JL\x07\x04\x00\x00\x004\x10\x01\nSerialized\x1f\x0b\xd9\x94\xf2J\xc2k"\xa7\xf4_r\xb0\xcd\x19\xa8\n\x01\x06SQLiteD4\x10\x01\x08DateTime\x1f\x0bj\xe02\x94\t\xdc\xfb\x98EY\x918p\xca\xe2\xad\x01\x05DatesD4\x10\x01\tUTInstant\x1f\x0bj\xe02\x94\t\xdc\xfb\x98EY\x918p\xca\xe2\xad\x01\x05DatesD\x01\x00\x00\x00\x10\x01\x0bMillisecond\x1f\x0bj\xe02\x94\t\xdc\xfb\x98EY\x918p\xca\xe2\xad\x01\x05DatesD4,\x06\x00\x08\x80\t\x07\x15\xd59\x00\x00' which to me is vaguely intelligible as some sort of byte representation of the julia datetime type. My first instinct is that its perhaps corrupted. But every single row follows a similar format, and furthermore, it looks like Julia reads it just fine julia> SQLite.query(ssdb, "select datetime from streetsegmentinfraction limit 5") 5×1 DataFrame │ Row │ datetime │ ├─────┼─────────────────────┤ │ 1 │ 2016-01-01T00:00:00 │ │ 2 │ 2016-01-01T00:01:00 │ │ 3 │ 2016-01-01T00:02:00 │ │ 4 │ 2016-01-01T00:02:00 │ │ 5 │ 2016-01-01T00:02:00 │ I googled around for what kind of encoding this might be, but didn’t find anything that looked helpful, which may be due to me not knowing what to look for. If it’s any help, I’m on Julia 1.0, running on a linux machine. I’d appreciate any leads on this. Cheers!

DateTimes in SQLite.jl use julia’s builtin serialize format; for compat with other sqlite clients, you’d probably be best off to call string on the values first.

Not really. I should probably special-case Date/DateTimes in SQLite.jl to store them as text anyway since they’re common and the interop w/ other languages is more important. The use of Julia’s serialize should really only be used for custom Julia types that would be more obviously tied to using Julia itself to query.

I opened an issue so I don’t forget: Convert date/datetimes to text when storing · Issue #160 · JuliaDatabases/SQLite.jl · GitHub

Datetimes and SQLite.jl encoding

General Usage

mosiman October 17, 2018, 2:53am 3

Thanks, I think that’s the easiest way forward for me. For completeness, are there any alternative ways to go about reading the serealized format in other programs?

Topic		Replies	Views
Dataframe encoding New to Julia question	6	1436	February 17, 2019
How load date on SQLite General Usage package	1	342	September 15, 2021
Unable to successfully read sqlserver Date datatype [ODBC, DataFrames] New to Julia data , database	1	550	October 20, 2021
Query on date doesn't return what expected New to Julia sqlite	25	902	November 16, 2024
SQLite, DataFrame and Missing Data	3	853	November 28, 2021

Datetimes and SQLite.jl encoding

Related topics