[BUG] Improper Handling of `datetime`-Typed Objects

[TheCedarPrince]

Hi all,

Running into a strange type issue with datetimes.

Is your feature request related to a problem? Please explain.

I am using the Eunomia sqlite database for some prototyping of ideas and for some reason, the birth_datetime field is misinterpreted as a Float object. Even if I tried to modify the query to Fun.datetime(Get.birth_datetime), this results in the objects being reported missing in my resulting dataframe.

To Reproduce

Julia Version (i.e. output of julia -v): 1.6.1

Operating system (Mac, Linux, Windows): macOS Catalina 10.15.7

FunSQL Version: FunSQL v0.8.0

Minimum working code example that led to bug:

using RCall
path_to_db = mktempdir() * "file.sqlite"

R"""
library("Eunomia")
Eunomia::getEunomiaConnectionDetails(databaseFile = $(path_to_db))
"""

person = SQLTable(
    :person,
    columns = [
        :person_id,
        :gender_concept_id,
        :year_of_birth,
        :month_of_birth,
        :day_of_birth,
        :birth_datetime,
        :race_concept_id,
        :ethnicity_concept_id,
        :location_id,
        :provider_id,
        :care_site_id,
        :person_source_value,
        :gender_source_value,
        :gender_source_concept_id,
        :race_source_value,
        :race_source_concept_id,
        :ethnicity_source_value,
        :ethnicity_source_concept_id,
    ],
)

sql =
    From(person) |>
    Select(
        Get.person_id,
        Get.gender_concept_id,
        Get.year_of_birth,
        Get.month_of_birth,
        Get.day_of_birth,
        Get.birth_datetime,
        Get.race_concept_id,
        Get.ethnicity_concept_id,
        Get.location_id,
        Get.provider_id,
        Get.care_site_id,
        Get.person_source_value,
        Get.gender_source_value,
        Get.gender_source_concept_id,
        Get.race_source_value,
        Get.race_source_concept_id,
        Get.ethnicity_source_value,
        Get.ethnicity_source_concept_id,
    ) |>
    x -> render(x, dialect = :sqlite)

result = SQLite.DB(path_to_db) |> 
     eunomia -> DBInterface.execute(eunomia, sql) |> DataFrame

Expected Behavior and Actual Behavior

I expected the datetime object to be converted to a Julia DateTime object in Julia. Instead, the datetime is either converted to a Float or missing.

[xitology]

This is the expected behavior. In Eunomia, datetime values are stored as float numbers, the number of seconds since 1970.

It is possible to convert birth_datetime to a date string with DATE(birth_datetime, 'unixepoch') (Fun.date(Get.birth_datetime, "unixepoch") in FunSQL), but this will give you back a String, not a Date object, so you'll have to convert it to Date manually in Julia code. This is the issue with SQLIte, which does not have native support for datetime values. Some SQLite bindings provide a way to recognize datetime values based on the declared column type or the column name (e.g., Python), but apparently not SQLIte.jl.

[TheCedarPrince]

Ah - sorry about that. Thought the issue was more on the FunSQL side. This is an interesting quirk I wasn't aware of in the SQLite.jl package. I'll open an issue about this on SQLite.jl side. Thanks @xitology ! Super helpful!