decode_time issue on myxql

[byronpc]

i've seen the previous issue...but can you please help me with this...i'm also getting this error on a couple of queries (but it's hard to pinpoint which queries they are) as the stack doesn't include our own library

MyXQL.Protocol.Values.decode_time(<<8, 0, 14, 0, 0, 0, 15, 2, 20>>, 0, [], [4397573])
MyXQL.Protocol.Values.decode_time(<<8, 0, 13, 0, 0, 0, 15, 49, 15>>, 0, [], [4397597])
MyXQL.Protocol.Values.decode_time(<<8, 0, 15, 0, 0, 0, 15, 14, 53>>, 0, [], [4397618])

these are sample queries where the decode_time failed

Originally posted by @byronpc in #95 (comment)

[byronpc]

ok. i get it now...it's due to me using fragment("TIMEDIFF(now(), ?), someolddatetime)

If i'm not mistaken on the old driver, it didn't return a time object but rather a map with %{hour: ..., minute: ...}. That's probably why it worked. Anyway I suppose this is still an issue right? Though quite complicated if we're fixed on using native time objects... anyway on my side i just used TIMESTAMPDIFF instead. though i'm not sure if you guys have plans to fully support this.

[josevalim]

@byronpc can you please include what is the MySQL and MyXQL adapter versions that you are using and please include a full example of the query that you are running? Thank you.

[wojtekmach]

If i'm not mistaken on the old driver, it didn't return a time object but rather a map with %{hour: ..., minute: ...}. That's probably why it worked.

Looks like Mariaex simply ignores days and negative values:

iex> Mariaex.query!(pid, "SELECT TIMEDIFF(?, ?)", [~N[2020-01-31 09:00:00], ~N[2020-01-31 08:00:00]]).rows
[[~T[01:00:00]]]

iex> Mariaex.query!(pid, "SELECT TIMEDIFF(?, ?)", [~N[2020-01-31 09:00:00], ~N[2020-01-31 10:00:00]]).rows
[[~T[01:00:00]]]

iex> Mariaex.query!(pid, "SELECT TIMEDIFF(?, ?)", [~N[2020-01-31 09:00:00], ~N[2020-01-01 08:00:00]]).rows
[[~T[01:00:00]]]

and I'd argue that's a bug.

Here's how myxql master handles exact same queries:

iex> MyXQL.query!(pid, "SELECT TIMEDIFF(?, ?)", [~N[2020-01-31 09:00:00], ~N[2020-01-31 08:00:00]]).rows
[[~T[01:00:00]]]

iex> MyXQL.query!(pid, "SELECT TIMEDIFF(?, ?)", [~N[2020-01-31 09:00:00], ~N[2020-01-31 10:00:00]]).rows
** (ArgumentError) cannot decode "-01:00:00" as time, negative or >= 24:00:00 values are not supported

iex> MyXQL.query!(pid, "SELECT TIMEDIFF(?, ?)", [~N[2020-01-31 09:00:00], ~N[2020-01-01 08:00:00]]).rows
** (ArgumentError) cannot decode "30d 01:00:00" as time, negative or >= 24:00:00 values are not supported

About:

a map with %{hour: ..., minute: ...}

yes, that'd be the way to handle this properly. To be precise the map would be something like %{days: ..., hours: ..., minutes: ..., seconds: ..., microseconds: ...} and possibly another field to mark it as negative.

It's a real shame that mysql uses the same type for time and duration and currently we only handle the former. I think we could support returning duration but it would need to be explicit so that we don't break existing code that does not expect duration. This could work:

iex> params = [~N[2020-01-31 09:00:00], ~N[2020-01-01 08:00:00]]
iex> MyXQL.query!(pid, "SELECT TIMEDIFF(?, ?)", params, [time: :as_duration])
%{days: 30, hours: 1, minutes: 0, seconds: 0, microseconds: 0}

but then of course the problem is what if a query wants to return both a time and a duration value? Either the user would then afterwards change duration to time struct manually or the option to return duration would be on a per-column basis.

I think we could make this work but as you can see it's not trivial and thus not on our roadmap currently. I could have totally missed some things so any feedback on this is appreciated!

[wojtekmach]

I think the way to solve this issue today is on the DB side: converting time diff into seconds:

iex> params = [[~N[2020-01-31 09:00:00], ~N[2020-01-31 10:00:00]]
iex> MyXQL.query!(pid, "SELECT TIME_TO_SEC(TIMEDIFF(?, ?))", params).rows
[[-3600]]

[byronpc]

yep sorry for the late reply. yes i did use timestampdiff() and it works as well