SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.1.276 Pro] - Timediff in MySQL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.1.276 Pro] - Timediff in MySQL
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [SA 9.1.276 Pro] - Timediff in MySQL Reply with quote
The results for timediff function in MySQL are not displayed properly. Actually, they're not displayed at all, the column shows NULL.
Mon May 15, 2017 8:39 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
It appears that SQL Assistant doesn't support TIME data type in MySQL. The value needs to be converted to CHAR so that it can be displayed

This returns null string
SELECT TIMEDIFF('2017:01:01 00:00:00', '2016:01:01 00:00:00.000001');

This returns the time difference
SELECT CONVERT(TIMEDIFF('2017:01:01 00:10:00', '2017:01:01 00:05:00'), CHAR);
Tue May 16, 2017 1:46 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Yes, but that's a workaround that would only work when one wants to display the value. Explicitly casting the type into another one will introduce tons of side-effects in subsequent code parts in case it is accidentally left there after checking the actual value. Also, the NULL that's shown in place of the real value might as well be a valid result. In my opinion, queries that contain columns of types not supported by SA should either fail with an error message stating this or at least show a warning instead of returning incorrect result set and concealing this error.
Tue May 16, 2017 3:27 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
I'm just speculating that there should be a catch-all rule for displaying of all "unknown" data-types which should attempt to convert data values to strings, and return an error of such is returned by the driver. In this case the result is an empty string without a hard error. I submitted an enactment request yesterday #SA0031372 to implement support for TIME data type in MySQL.
Tue May 16, 2017 9:18 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Confirmed as fixed is [9.2.346 Pro].
Sat Jul 15, 2017 8:00 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.