Re: Storing dates

From	Jerry Stuckle <jstucklex@attglobal.net>
Newsgroups	comp.databases.mysql, comp.lang.php
Subject	Re: Storing dates
Date	2014-06-21 17:49 -0400
Organization	A noiseless patient Spider
Message-ID	<lo4ulg$11b$1@dont-email.me> (permalink)
References	(8 earlier) <hzk4wa449wa$.3xtj0jkxz78o$.dlg@40tude.net> <XnsA353998B95688dougmilmaccom@78.46.70.116> <210620142206250710%timstreater@greenbee.net> <lo4sa4$h1p$1@dont-email.me> <210620142233257893%timstreater@greenbee.net>

Cross-posted to 2 groups.

Show all headers | View raw

On 6/21/2014 5:33 PM, Tim Streater wrote:
> In article <lo4sa4$h1p$1@dont-email.me>, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
> 
>> On 6/21/2014 5:06 PM, Tim Streater wrote:
>> > In article <XnsA353998B95688dougmilmaccom@78.46.70.116>, Doug Miller
>> > <doug_at_milmac_dot_com@example.com> wrote:
>> > >> richard <noreply@example.com> wrote in
>> >> news:hzk4wa449wa$.3xtj0jkxz78o$.dlg@40tude.net:
>> >> > On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>> >> > >> richard <noreply@example.com> wrote in
>> >> >> news:1mddkhwt2mr0v$.1m6eithcu7iod.dlg@ 40tude.net:
>> >> >> >>> As for the date usage on MY database tables, I am only using a
>> >> >>> date as a reference.
>> >> >> >> Patently false, as you have posted questions here asking for
>> >> >> help resolving your problems in sorting by the date.
>> >> >> >>> I really don't give a damn what your philosophy or ideals are
>> >> >>> behind the so called "proper ways" of obtaining a date.
>> >> >>  >> Which is exactly *why* you have problems sorting by the date.
>> >> > > The problem with sorting is, it treats any value given as a
>> >> > string, then determines which has the lowest value in total.
>> >>
>> >> Yes, I understand that. That results directly from storing dates as
>> >> strings,
>> >> and is exactly the reason that everyone here has been telling you to
>> >> store your dates in a
>> >> column that has
>> >> the DATE datatype, not CHAR.
>> >> Your refusal to follow that advice -- apparently stemming from your
>> >> inability
>> >> to understand that there even *are* datatypes other than NUMERIC and
>> >> CHAR -- is the entire reason you are having this sorting problem.
>> > > I have a field which contains values like 3.7, 4.2, etc. But when I
>> > defined the field (in SQLite) I made it a TEXT field, forgetting that I
>> > would need to be able to sort on that field.
>> > > My workaround is to convert the string to floating at the time of the
>> > sort. In SQLite I do:
>> > >  ... ORDER BY ROUND(myfield,1);
>> > > which works just fine. Richard should look for something similar in
>> > mysql.
>>
>> Why don't you fix your database?
>>
>> The way you're doing it cannot use an index and will call round() for
>> EVERY row in the sort.
> 
> I'd have to ask a number of users to convert their data when they next
> upgrade the software. Trouble is in SQLite you can't just redefine a
> table column to be a different type; ALTER TABLE is somewhat limited.
> I'd have to copy the whole table to another, DROP the first one, RENAME
> the new one, VACUUM the database to get its size down. OK, it's doable,
> but I'm replying on less-than-technically-savvy users to run a Terminal
> script on their own machine to effect this change.
>

Or have a script do it for them.

> In my own use of the app, I've got 65 databases that an update script
> would have to find, recursively in an arbitrary folder structure. One
> user has 1000. The recursion part is actually easy, that app already
> does that at startup to verify that the user hasn't moved some
> databases around by hand while the app wasn't running. I just feel a
> shade nervous about distant people (i.e., not me) running a one-off
> script.
> 

People do it every day.  Just ensure everything is backed up before
doing any conversion.

> In richard's case, it didn't sound like he has a huge number of rows,
> so it struck me that a workaround wouldn't have much of a performance
> impact. In mine, the column in question is unlikely to be a popular one
> for sorting.
> 

ANYTHING with richard is a problem.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

Thread

Putting it all together richard <noreply@example.com> - 2014-06-20 10:20 -0400
  Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 08:11 -0700
    Re: Putting it all together richard <noreply@example.com> - 2014-06-20 11:24 -0400
      Re: Putting it all together Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-20 17:15 +0000
      Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 20:01 -0700
  Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 08:21 -0700
  Re: Putting it all together Norman Peelman <npeelman@cfl.rr.com> - 2014-06-20 21:06 -0400
  Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 03:02 +0000
  Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 03:09 +0000
  Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 20:13 -0700
    Re: Putting it all together richard <noreply@example.com> - 2014-06-20 23:34 -0400
      Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 20:48 -0700
      Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 21:00 -0700
      Re: Putting it all together Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-21 04:22 +0000
        Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-20 22:11 -0700
          Re: Putting it all together Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-20 22:39 -0700
        Re: Putting it all together gordonb.zp2md@burditt.org (Gordon Burditt) - 2014-06-21 04:15 -0500
          Storing dates (was: Putting it all together) Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-21 12:15 +0200
            Re: Storing dates richard <noreply@example.com> - 2014-06-21 11:10 -0400
              Re: Storing dates Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-21 17:42 +0000
                Re: Storing dates richard <noreply@example.com> - 2014-06-21 14:28 -0400
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 12:04 -0700
                Re: Storing dates Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-21 19:06 +0000
                Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-21 22:06 +0100
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-21 17:09 -0400
                Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-21 22:33 +0100
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-21 17:49 -0400
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-22 14:38 +0200
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 14:31 -0700
                Re: Storing dates Tim Streater <timstreater@greenbee.net> - 2014-06-21 22:44 +0100
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-22 14:59 +0200
                Re: Storing dates Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 23:19 +0000
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 08:53 +0200
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-22 08:03 +0000
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-22 09:29 -0400
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-22 14:24 +0000
                Re: Storing dates Jerry Stuckle <jstucklex@attglobal.net> - 2014-06-22 10:32 -0400
                Re: Storing dates "Erick T. Barkhuis" <erick.use-net@ardane.c.o.m> - 2014-06-22 14:45 +0000
                Re: Storing dates Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-22 19:44 +0000
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-22 08:26 -0700
                Re: Storing dates gordonb.q26od@burditt.org (Gordon Burditt) - 2014-06-22 10:00 -0500
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 16:47 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 13:55 -0400
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-22 12:31 -0700
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-24 22:27 +0200
                Re: Storing dates Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-22 19:52 +0000
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 21:52 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 14:05 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 21:56 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 22:46 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 08:11 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-23 02:37 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 11:10 +0200
                Re: Storing dates Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-23 06:50 -0700
                Re: Storing dates richard <noreply@example.com> - 2014-06-23 11:13 -0400
                Re: Storing dates Evan Platt <evan@theobvious.espphotography.com.invalid> - 2014-06-23 09:13 -0700
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 22:33 +0200
                Re: Storing dates richard <noreply@example.com> - 2014-06-22 23:04 -0400
                Re: Storing dates Arno Welzel <usenet@arnowelzel.de> - 2014-06-23 08:12 +0200
                Re: Storing dates Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2014-06-23 16:45 +1000
                Re: Storing dates richard <noreply@example.com> - 2014-06-23 07:58 -0400
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-23 09:50 -0700
                Re: Storing dates Christoph Michael Becker <cmbecker69@arcor.de> - 2014-06-23 19:02 +0200
                Re: Storing dates Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2014-06-24 12:00 +1000
                Re: Storing dates Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-06-24 22:38 +0200
                Re: Storing dates Jürgen Exner <jurgenex@hotmail.com> - 2014-06-22 15:30 -0700
                Re: Storing dates "Peter H. Coffin" <hellsop@ninehells.com> - 2014-06-22 20:10 -0500
          Re: Putting it all together Jürgen Exner <jurgenex@hotmail.com> - 2014-06-21 03:34 -0700
      Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 10:23 +0000
      Re: Putting it all together bill <william@TechServSys.com> - 2014-06-21 07:03 -0400
        Re: Putting it all together Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-21 15:11 +0000
      Re: Putting it all together "Peter H. Coffin" <hellsop@ninehells.com> - 2014-06-21 10:57 -0500
      Re: Putting it all together Arno Welzel <usenet@arnowelzel.de> - 2014-06-22 08:45 +0200

csiph-web