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

5

The problem is with https://github.com/JuliaDatabases/MySQL.jl/blob/c514ff68efaf984f5ec685a205c80dc42fd40a6b/src/execute.jl#L69 that calls Parsers.jl. Probably the reason is as commented above with the date format. I guess that @quinnj can diagnose the issue best.

6

Is there a workaround to this?

7

I will try to ping @quinnj to have a look at it.

8

There’s a new keyword argument to support sub-millisecond timestamps coming from MySQL; you can do: DBInterface.execute(conn, sql; mysql_date_and_time=true). This will result in timestamp columns being of type MySQL.DateAndTime, which is simply defined as:

struct DateAndTime <: Dates.AbstractDateTime
    date::Date
    time::Time
end

so you can unpack that struct into separate Date and Time components as needed.

3 Likes
9

FYI, my workaround was to do this in the mysql query select DATE(ts) as date, time_format(time(ts),'%T')) - but I like your solution much better!