# Parsing of mysql timestamps
**URL:** https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587
**Category:** Data
**Tags:** dates
**Created:** [March 8, 2022, 1:46pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587 "2022-03-08T13:46:05Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@linusheinz](https://discourse.julialang.org/u/linusheinz)
#### Post date: [March 8, 2022, 1:46pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/1 "2022-03-08T13:46:06Z")
I am querying a table with a timestamp:
```julia
data = DBInterface.execute(database, sql)
df = DataFrame(data)
```
But I get :
```julia
ERROR: LoadError: error parsing DateTime from "2022-01-04 15:08:44.171540"
Stacktrace:
```
How can I tell DataFrame how to parse these timestamps
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [March 8, 2022, 2:39pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/2 "2022-03-08T14:39:36Z")
`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.
---
### Author:  [@lawless-m](https://discourse.julialang.org/u/lawless-m)
#### Post date: [March 8, 2022, 2:44pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/3 "2022-03-08T14:44:30Z")
You need to truncate the string first, Julia can only cope with 3dp for subseconds in this kind of conversion
```julia
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)
```
---
### Author:  [@linusheinz](https://discourse.julialang.org/u/linusheinz)
#### Post date: [March 8, 2022, 4:25pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/4 "2022-03-08T16:25:32Z")
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.
```julia
[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
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [March 8, 2022, 4:53pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/5 "2022-03-08T16:53:16Z")
The problem is with [https://github.com/JuliaDatabases/MySQL.jl/blob/c514ff68efaf984f5ec685a205c80dc42fd40a6b/src/execute.jl#L69](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.
---
### Author:  [@jerlich](https://discourse.julialang.org/u/jerlich)
#### Post date: [December 20, 2022, 4:01pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/6 "2022-12-20T16:01:46Z")
Is there a workaround to this?
---
### Author:  [@bkamins](https://discourse.julialang.org/u/bkamins)
#### Post date: [December 20, 2022, 4:17pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/7 "2022-12-20T16:17:41Z")
I will try to ping @quinnj to have a look at it.
---
### Author:  [@quinnj](https://discourse.julialang.org/u/quinnj)
#### Post date: [December 20, 2022, 7:44pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/8 "2022-12-20T19:44:24Z")
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:
```julia
struct DateAndTime <: Dates.AbstractDateTime
date::Date
time::Time
end
```
so you can unpack that struct into separate `Date` and `Time` components as needed.
---
### Author:  [@jerlich](https://discourse.julialang.org/u/jerlich)
#### Post date: [December 20, 2022, 9:42pm UTC](https://discourse.julialang.org/t/parsing-of-mysql-timestamps/77587/9 "2022-12-20T21:42:50Z")
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!