[mySQL] How to remove Fractional Seconds

[Kay C Lan]

Thanks Peter, saved the day!!! Yes, having already set the Date and Time
format on db start up it is a bit of a hassle to again specify it every
time you use a date/time function.

Oh, in my hurry to solve this I was so rude to completely forget; Mary
Christmas and Harry New Year to everyone on the List. I'm sure you all made
it on Santa's good list so I hope you're all enjoying the season's goodies
:-)


On Fri, Dec 27, 2013 at 2:12 AM, Peter Haworth <pete at lcsql.com> wrote:

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