[mySQL] How to remove Fractional Seconds

[Kay C Lan]

For the mySQL gurus.

Have just updated from mySQL 5.5.x to 5.6.15 and see the root cause of my
problem is that since 5.6.4 mySQL allows a fractional second part (fsp) at
the end of times where as prior to this they were ignored. The latest
version of mySQL allows dateTime, and time columns to be defined to include
a fsp. My columns are old and do NOT include a fsp definition.

So:

SELECT startDT FROM schedule WHERE meeting = 'pointless'

2013-12-25 09:00:00
2013-12-26 09:00:00
2013-12-25 11:30:00
2013-12-26 11:30:00
2013-12-25 14:00:00
2013-12-25 17:00:00

As expected, same as my old 5.5.x, but if I do this:

SELECT DISTINCT time(StartDT) FROM schedule WHERE meeting = 'pointless'

09:00:00.000000
11:30:00.000000
14:00:00.000000
17:00:00.000000

I now get fsp added to the time. I've since learnt that I can specify
fspprecision:

SELECT NOW()

2013-12-26 15:14:13

SELECT NOW(6)

2013-12-26 15:14:13.987654

SELECT NOW(0)

2013-12-26 15:14:13

So what I want to be able to do is use time() and specify 0 fsp:

SELECT DISTINCT time(StartDT,0) FROM schedule WHERE meeting = 'pointless'

OR

SELECT DISTINCT time(0,StartDT) FROM schedule WHERE meeting = 'pointless'

but neither of these work.

How can I use the mySQL time() function AND specify no fsp?

Is there a mySQL variable that I can set to return it to pre 5.6.4
functionality?

I'm looking for a single mySQL statement solution.

Whilst I appreciate that this may appear easy for LiveCode to fix, in this
context I'd really prefer to solve this in mySQL. Basically I have a very
long and convoluted script which builds 99 different SQL statements which
progressively get more and more complex based on the result of the previous
query. char -2 to -1 has always given me the seconds no matter the result
is a DateTime, Time or time() output. Now I'm sporadically getting 00 when
it should be a much larger number and it's screwing the results completely.
Thankfully the final result was far enough off expectations that it
prompted me to double check.

I'm even considering downgrading to 5.6.3 because I've literally just
upgraded, opened my first LC Stack and hit this problem within a minute. I
do a LOT of LC + mySQL + dates and times, so I know this is just the tip of
the iceberg.

Thanks