Datetimes and SQLite.jl encoding

General Usage
1

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!

2

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.

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?

4

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.

1 Like
5

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