[mySQL] How to remove Fractional Seconds

Peter Haworth pete at lcsql.com
Thu Dec 26 11:30:52 EST 2013


Maybe the DATE-FORMAT function will help?

Pete
lcSQL Software
On Dec 25, 2013 5:59 PM, "Kay C Lan" <lan.kc.macmail at gmail.com> wrote:

> 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
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



More information about the use-livecode mailing list