Parsing of mysql timestamps

Specific Domains Data
#1

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

#2

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.

#3

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)
#4

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

TimesDates.jl dev for v2
#5

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.