[mySQL] How to remove Fractional Seconds

[Peter Haworth]

That should, of course, have been DATE_FORMAT, or TIME _FORMAT will work
equally well.

Another thought - SELECT DISTINCT substr(time(StartDT,1,8)).....

It does seem strange that you have to jump through artificial hoops like
this to get what you want.

Pete
lcSQL Software <http://www.lcsql.com>


On Thu, Dec 26, 2013 at 8:30 AM, Peter Haworth <pete at lcsql.com> wrote:

> 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
>>
>