I am querying a table with a timestamp:
data = DBInterface.execute(database, sql)
df = DataFrame(data)
But I get :
ERROR: LoadError: error parsing DateTime from "2022-01-04 15:08:44.171540"
Stacktrace:
How can I tell DataFrame how to parse these timestamps
DataFrame
constructor does not parse timestamps. Please show a full stack trace, so that it is possible to identify the source of the problem. Most likely it is on data base driver side.
You need to truncate the string first, Julia can only cope with 3dp for subseconds in this kind of conversion
julia> DateTime("2022-01-04 15:08:44.171540"[1:23], "yyyy-mm-dd HH:MM:SS.sss")
2022-01-04T15:08:44.171
julia> DateTime("2022-01-04 15:08:44.171540"[1:24], "yyyy-mm-dd HH:MM:SS.ssss")
ERROR: InexactError: convert(Dates.Decimal3, 1715)
Thanks for the replies. I thought that Dataframes did’nt cast types as well it is probaly the DBInterface. This is the full stacktrace. The columnames are obfuscated and replaced by :Columns.
[1] error(s::String)
@ Base .\error.jl:33
[2] casterror(T::Type, ptr::Ptr{UInt8}, len::UInt32)
@ MySQL C:\Users\lheinz\.julia\packages\MySQL\0vHyV\src\execute.jl:53
[3] cast(#unused#::Type{DateTime}, ptr::Ptr{UInt8}, len::UInt32)
@ MySQL C:\Users\lheinz\.julia\packages\MySQL\0vHyV\src\execute.jl:76
[4] cast
@ C:\Users\lheinz\.julia\packages\MySQL\0vHyV\src\execute.jl:32 [inlined]
[5] getcolumn
@ C:\Users\lheinz\.julia\packages\MySQL\0vHyV\src\execute.jl:100 [inlined]
[6] eachcolumns
@ C:\Users\lheinz\.julia\packages\Tables\PxO1m\src\utils.jl:111 [inlined]
[7] buildcolumns(schema::Tables.Schema{(:Columnames), Tuple{String, Union{Missing, String}, Union{Missing, String}, Union{Missing, String}, Union{Missing, String}, Union{Missing, String}, Union{Missing, String}, Union{Missing, String}, Union{Missing, String}, Union{Missing, DateTime}}}, rowitr::MySQL.TextCursor{true})
@ Tables C:\Users\lheinz\.julia\packages\Tables\PxO1m\src\fallbacks.jl:135
[8] columns
@ C:\Users\lheinz\.julia\packages\Tables\PxO1m\src\fallbacks.jl:251 [inlined]
[9] DataFrame(x::MySQL.TextCursor{true}; copycols::Nothing)
@ DataFrames C:\Users\lheinz\.julia\packages\DataFrames\MA4YO\src\other\tables.jl:58
[10] DataFrame(x::MySQL.TextCursor{true})
@ DataFrames C:\Users\lheinz\.julia\packages\DataFrames\MA4YO\src\other\tables.jl:49
[11] top-level scope
@ c:\Users\lheinz\FlowDashboard.jl\test.jl:40
in expression starting at c:\Users\lheinz\FlowDashboard.jl\test.jl:40
Thanks for all the help
The problem is with MySQL.jl/execute.jl at c514ff68efaf984f5ec685a205c80dc42fd40a6b · JuliaDatabases/MySQL.jl · GitHub that calls Parsers.jl. Probably the reason is as commented above with the date format. I guess that @quinnj can diagnose the issue best.