Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #13930 > unrolled thread

Putting it all together

Back to article view | Back to comp.lang.php

Contents

  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

Page 2 of 4 — ← Prev page 1 [2] 3 4  Next page →

#13965 — Re: Storing dates

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.
1
2
3
10
20
30
100
200
300

When you sort these numbers, how do they get displayed?
You will most likely see them displayed as:
1
10
100
2
Or maybe even as 
100
10
1
2

In Liberty BASIC I got (as strings)
1
10
100
2
20
200
3
30
300

[toc] | [prev] | [next] | [standalone]

#13966 — Re: Storing dates

On Sat, 21 Jun 2014 14:28:47 -0400, richard <noreply@example.com> wrote
in comp.databases.mysql:

>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.
>1
>2
>3
>10
>20
>30
>100
>200
>300

A normal ascending numerical sort

>When you sort these numbers, how do they get displayed?
>You will most likely see them displayed as:
>1
>10
>100
>2

A normal ascending alphabetical sort

>Or maybe even as 
>100
>10
>1
>2

This looks rather strange. Even if there were a trailing space character
then still "10 " would be sorted before "100 ". What sorting is this? 

>In Liberty BASIC I got (as strings)
>1
>10
>100
>2
>20
>200
>3
>30
>300

Again, a standard alphabetical sort.

So, which sorting order do you want? Numerical or alphabetical? And is
your data stored as number or as string? And if the type doesn't match
then what happened when you cast it into the right data type?

jue

[toc] | [prev] | [next] | [standalone]

#13967 — Re: Storing dates

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. 

[toc] | [prev] | [next] | [standalone]

#13970 — Re: Storing dates

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.

-- 
"If you're not able to ask questions and deal with the answers without feeling 
that someone has called your intelligence or competence into question, don't 
ask questions on Usenet where the answers won't be carefully tailored to avoid
tripping your hair-trigger insecurities."                  - D M Procida, UCSM

[toc] | [prev] | [next] | [standalone]

#13971 — Re: Storing dates

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.

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

[toc] | [prev] | [next] | [standalone]

#13973 — Re: Storing dates

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.

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.

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.

-- 
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[toc] | [prev] | [next] | [standalone]

#13975 — Re: Storing dates

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

[toc] | [prev] | [next] | [standalone]

#13984 — Re: Storing dates

[F'up2 comp.databases]

Tim Streater wrote in comp.databases.mysql and comp.lang.php:

> Jerry Stuckle wrote:
>> On 6/21/2014 5:06 PM, Tim Streater wrote:
>> > 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.

True, but see also “Making Other Kinds Of Table Schema Changes” in 
<http://www.sqlite.org/lang_altertable.html>.

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

If this is for an Android app (where SQLite is prevalent), you can use 
built-in versioning support to migrate the SQLite database table structure 
automagically with the next upgrade or downgrade.  BTDT.

<http://developer.android.com/guide/topics/data/data-storage.html#db>
<http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html>

Otherwise you can have a table with corresponding version information.  If 
it is missing or the corresponding record is missing, you can assume the old 
format and migrate the data accordingly.  Several SQL-based applications do 
this. 

For example, Magento (which by default is MySQL-InnoDB-based) holds the 
version of an extension in the table `core_resource`, and you can write 
migration scripts (named e.g. upgrade-$old_ver-$new_ver.php) that are 
automatically run if the current version differs but matches $old_ver, and 
the new extension version matches $new_ver, after which the version in 
`core_resource` is updated automatically to $new_ver.


PointedEars 
-- 
Sometimes, what you learn is wrong. If those wrong ideas are close to the 
root of the knowledge tree you build on a particular subject, pruning the 
bad branches can sometimes cause the whole tree to collapse.
  -- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom@94.75.214.39>

[toc] | [prev] | [next] | [standalone]

#13972 — Re: Storing dates

On Sat, 21 Jun 2014 22:06:25 +0100, Tim Streater
<timstreater@greenbee.net> wrote in comp.databases.mysql:
>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. 

Yes, that is a very well-known workaround commonly known as casting.

>Richard should look for something similar in
>mysql.

If he is using Julian days, then yes. Of course he could and should have
used a numerical type for this field in the first place. Then as you
discovered yourself he would not have to cast the value to a different
type.
If he is using a textual representation of a Julian calender date then
no. There he is completely on his own.
And if he had been using DATE or DATETIME then there wouldn't even be
this discussion.

jue

[toc] | [prev] | [next] | [standalone]

#13974 — Re: Storing dates

In article <d3ubq91a9ufstpi1fp963r54684sue6cr9@4ax.com>, Jürgen Exner
<jurgenex@hotmail.com> wrote:

> On Sat, 21 Jun 2014 22:06:25 +0100, Tim Streater
> <timstreater@greenbee.net> wrote in comp.databases.mysql:
> >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. 
> 
> Yes, that is a very well-known workaround commonly known as casting.

Are you implying that I might instead do:

  ... ORDER BY CAST (myfield AS REAL);

?

-- 
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[toc] | [prev] | [next] | [standalone]

#13985 — Re: Storing dates

[F'up2 comp.databases]

J�rgen Exner wrote in comp.databases.mysql and comp.lang.php:
^^^^^^^^^^^^
Please do not use non-ASCII characters (like umlauts) in header field 
values unless you are prepared to properly MIME-encode them as RFC 5322, and 
by reference RFC 5536, requires it.  Forté Agent is not smart enough for 
that, at least not the outdated version of it that you are using.

> On Sat, 21 Jun 2014 22:06:25 +0100, Tim Streater
> <timstreater@greenbee.net> wrote in comp.databases.mysql:

It's attribution *line*, _not_ attribution novel.

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

By coincidence.

> Yes, that is a very well-known workaround commonly known as casting.

In this case the value is cast to an IEEE-754 double, probably resulting in 
different values.

<http://www.sqlite.org/faq.html#q16>


PointedEars
-- 
Sometimes, what you learn is wrong. If those wrong ideas are close to the 
root of the knowledge tree you build on a particular subject, pruning the 
bad branches can sometimes cause the whole tree to collapse.
  -- Mike Duffy in cljs, <news:Xns9FB6521286DB8invalidcom@94.75.214.39>

[toc] | [prev] | [next] | [standalone]

#13976 — Re: Storing dates

On Sat, 21 Jun 2014 14:28:47 -0400, richard wrote:

> The problem with sorting is, it treats any value given as a string, then
> determines which has the lowest value in total.

It only treats it as a string because you've defined it as data of type 
string.

If you had defined it as data of type date, then it would store it as 
data of type date (provided you delivered the data to it in a format that 
it recognised as valid for type date) and then sorting by date would work.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#13982 — Re: Storing dates

richard, 2014-06-21 20:28:

> 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.
> 1
> 2
> 3
> 10
> 20
> 30
> 100
> 200
> 300

That's why *thinking* about how to store information is important.

Even if you don't like to use a date datatype you should store the date
values in a way that they can be used for sorting - so year first, then
month, then day and all with leading zeros:

2014-06-22 for the 22. June of 2014
1967-09-12 for the 9. September of 1967

and so on.

BTW: This is why ISO 8601 had been invented - see
<http://en.wikipedia.org/wiki/ISO_8601>.


-- 
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de

[toc] | [prev] | [next] | [standalone]

#13983 — Re: Storing dates

Arno Welzel:

>1967-09-12 for the 9. September of 1967
>BTW: This is why ISO 8601 had been invented 

If that's the case, I don't like ISO 8601.



-- 
Erick

[toc] | [prev] | [next] | [standalone]

#13986 — Re: Storing dates

On 6/22/2014 4:03 AM, Erick T. Barkhuis wrote:
> Arno Welzel:
> 
>> 1967-09-12 for the 9. September of 1967
>> BTW: This is why ISO 8601 had been invented 
> 
> If that's the case, I don't like ISO 8601.
> 
> 
> 

Why not, Erick?  It's a good format which sorts naturally.

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

[toc] | [prev] | [next] | [standalone]

#13987 — Re: Storing dates

Jerry Stuckle:

>On 6/22/2014 4:03 AM, Erick T. Barkhuis wrote:
>> Arno Welzel:
>> 
>>> 1967-09-12 for the 9. September of 1967
>>> BTW: This is why ISO 8601 had been invented 
>> 
>> If that's the case, I don't like ISO 8601.
>
>Why not, Erick?  It's a good format which sorts naturally.

But apparently, it wants date formats to be three days off!
It might sort naturally, but it's not accurate enough.


-- 
Erick

[toc] | [prev] | [next] | [standalone]

#13988 — Re: Storing dates

On 6/22/2014 10:24 AM, Erick T. Barkhuis wrote:
> Jerry Stuckle:
> 
>> On 6/22/2014 4:03 AM, Erick T. Barkhuis wrote:
>>> Arno Welzel:
>>>
>>>> 1967-09-12 for the 9. September of 1967
>>>> BTW: This is why ISO 8601 had been invented 
>>>
>>> If that's the case, I don't like ISO 8601.
>>
>> Why not, Erick?  It's a good format which sorts naturally.
> 
> But apparently, it wants date formats to be three days off!
> It might sort naturally, but it's not accurate enough.
> 
> 

No, "9" is September.  The full date is September 12th, 1967.  The
difference in languages and punctuation is causing confusion.

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

[toc] | [prev] | [next] | [standalone]

#13989 — Re: Storing dates

Jerry Stuckle:

>On 6/22/2014 10:24 AM, Erick T. Barkhuis wrote:
>> Jerry Stuckle:
>> 
>>> On 6/22/2014 4:03 AM, Erick T. Barkhuis wrote:
>>>> Arno Welzel:
>>>>
>>>>> 1967-09-12 for the 9. September of 1967
>>>>> BTW: This is why ISO 8601 had been invented 
>>>>
>>>> If that's the case, I don't like ISO 8601.
>>>
>>> Why not, Erick?  It's a good format which sorts naturally.
>> 
>> But apparently, it wants date formats to be three days off!
>> It might sort naturally, but it's not accurate enough.
>> 
>> 
>
>No, "9" is September.  The full date is September 12th, 1967.  The
>difference in languages and punctuation is causing confusion.

Obviously!
Arno is german. In Germany, it's common to write a dot as abbreviation
for 'st', 'nd', 'rd' and 'th':
He would pronounce "9. September" as "der neunte September" (the ninth
of September).

[Now that I have to explain this, it ain't funny anymore: Arno wrote
1967-09-12 and claimed this to be equal to the ninth of September 1967.
That prompted me, since I sometimes like to see funny things where they
aren't, to claim that ISO 8601 is no good at all, if it defines date
formats three days off]



-- 
Erick

[toc] | [prev] | [next] | [standalone]

#13996 — Re: Storing dates

On Sun, 22 Jun 2014 10:32:09 -0400, Jerry Stuckle wrote:

> On 6/22/2014 10:24 AM, Erick T. Barkhuis wrote:
>> Jerry Stuckle:
>> 
>>> On 6/22/2014 4:03 AM, Erick T. Barkhuis wrote:
>>>> Arno Welzel:
>>>>
>>>>> 1967-09-12 for the 9. September of 1967 BTW: This is why ISO 8601
>>>>> had been invented
>>>>
>>>> If that's the case, I don't like ISO 8601.
>>>
>>> Why not, Erick?  It's a good format which sorts naturally.
>> 
>> But apparently, it wants date formats to be three days off!
>> It might sort naturally, but it's not accurate enough.

> No, "9" is September.  The full date is September 12th, 1967.  The
> difference in languages and punctuation is causing confusion.

Yes Jerry, but Arno wrote:

1967-09-12 for the 9. September of 1967

Which is the iso 8601 representation of 12th sep 1967 followed by the 
text representation of 9th sep 1967.

i.e. Arno made a booboo!

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#13992 — Re: Storing dates

On 22 Jun 2014 14:24:12 GMT, "Erick T. Barkhuis"
<erick.use-net@ardane.c.o.m> wrote in comp.databases.mysql:
>>>> 1967-09-12 for the 9. September of 1967
>
>But apparently, it wants date formats to be three days off!

:-)

Good one!!!

jue

[toc] | [prev] | [next] | [standalone]

Page 2 of 4 — ← Prev page 1 [2] 3 4  Next page →

Back to top | Article view | comp.lang.php

csiph-web