 Putting it all together [message #186209] |
Fri, 20 June 2014 14:20  |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
http://mroldies.net/radio/tracker4.php
For this excercise, I decided to order by artist.
I'll do it by date later.
I also gave up on trying to strip the slashes from the data as it got
inserted into the table.
I decided it would be easier to remove the slashes on the output.
stripslashes() worked rather nicely.
|
|
|
|
|
|
|
|
| Re: Putting it all together [message #186212 is a reply to message #186210] |
Fri, 20 June 2014 15:24  |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 08:11:51 -0700, Evan Platt wrote:
> On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com>
> wrote:
>
>> http://mroldies.net/radio/tracker4.php
>>
>> For this excercise, I decided to order by artist.
>> I'll do it by date later.
>
> You mean once Lew responds to your e-mail?
>
> Poor Lew. Might as well quit your day job. When bullis posts here and
> everyone refuses to help him, he'll be e-mailing you.
One stupid ignorant troll you are.
FYI, asswipe, Lew said to eamil him for further help.
I do not do email unless asked to.
Where have you had constructive input, to anything, in either group?
No where.
www.espphotography.com
Now conveninetly parked.
What a joke.
For you Mr.Miller, don't even consider telling me I can't call evan an
asswipe. Because he is one.
|
|
|
|
|
|
|
|
| Re: Putting it all together [message #186218 is a reply to message #186212] |
Sat, 21 June 2014 03:01 |
Evan Platt
Messages: 124
Registered: November 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 11:24:48 -0400, richard <noreply(at)example(dot)com>
wrote:
> One stupid ignorant troll you are.
Pot, kettle.
> FYI, asswipe, Lew said to eamil him for further help.
> I do not do email unless asked to.
Yeah, and I bet Lew's going to regret telling you that when you start
e-mailing him every day.
> Where have you had constructive input, to anything, in either group?
> No where.
And where have you?
> www.espphotography.com
> Now conveninetly parked.
> What a joke.
And how's your dome home coming? You know, the one you said would be
done in a year - 3 years ago?
What a joke.
> For you Mr.Miller, don't even consider telling me I can't call evan an
> asswipe. Because he is one.
bullis, stick to something you're good at. You know, like coloring.
--
To reply via e-mail, remove The Obvious and .invalid from my e-mail address.
|
|
|
|
| Re: Putting it all together [message #186219 is a reply to message #186209] |
Sat, 21 June 2014 03:02 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 10:20:59 -0400, richard wrote:
> For this excercise, I decided to order by artist.
> I'll do it by date later.
Having discovered that your past refusal to store date information in a
database as date information means that the database can't sort it in
date order, you're giving up on this one.
Note that you have in fact been given an example of how to get mysql to
sort your richardian string representation of date by actual date order,
by adding a conversion in the order by clause that converts the richardian
string representation of date into a true date that mysql can sort on.
When you ask how to do this again in a few days / weeks / months / aeons,
I reserve the non-exclusive[1] right to say "see the previous discussion
about this".
> I also gave up on trying to strip the slashes from the data as it got
> inserted into the table.
Given that we're talking about mysql, presumably the table you're
referring to is the database table. You should probably be using the
appropriate quoting and escaping functions that exist in the programming
environment that you are using to generate sql, rather than inventing
your own based on your flawed understanding of what needs to be done.
[1] Who wants shares in this?
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Putting it all together [message #186228 is a reply to message #186225] |
Sat, 21 June 2014 09:15 |
gordonb.zp2md
Messages: 1
Registered: June 2014
Karma: 0
|
Junior Member |
|
|
> Why would anyone store a date in a MySQL
> database using any data type other than DATE or DATETIME?
Believe it or not, there are (very rarely) good reasons to do that.
Genealogy is one example. It has lots of dates MySQL won't accept.
First, some people (such as English royalty) can trace their ancestry
back before 1000 A.D., before the limit on the DATE type. Second,
MySQL doesn't like imprecise dates, such as only a year being known.
Third, during a time when both the Julian and Gregorian calendars
were in use, depending on location, you often see dates recorded
as "5 January 1712/13" or "5 January 1712/3". You might also see
"5 January 1712".
Since the Julian calendar as used in the English colonies that later
became the United States used March 25 as the first day of the year,
you can get ridiculous-looking records such as a child born: March
27, 1700, died: March 23, 1700 (not a mistake: the child lived
almost a full year) and born: March 24, 1700, died: March 25, 1701
(the child lived about 24 hours). Also, a date recorded as "the
6th day of the third month of 1699" might be referring to March or
May.
It may be best to treat all dates (birth, death, marriage, graduation,
etc.) as ranges. These should be DATE types for efficient sorting
and date arithmetic. It's also important to record the date(s) as
stated in the source(s) as originally recorded, complete with
accurate reproduction of spelling errors and in the original language,
in text fields (or perhaps as an image). This is not redundant.
You may have to do considerable research to set the DATE fields
from the original source data that may be conflicting, imprecise,
or ambiguous. You might need to try extening the Gregorian calendar
backwards to avoid death before birth and other anomalies.
|
|
|
|
| Storing dates (was: Putting it all together) [message #186229 is a reply to message #186228] |
Sat, 21 June 2014 10:15 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
[F'up2 comp.databases.mysql
Gordon Burditt wrote in comp.lang.php and comp.databases.mysql:
> [“richard” wrote:]
>> Why would anyone store a date in a MySQL
>> database using any data type other than DATE or DATETIME?
>
> Believe it or not, there are (very rarely) good reasons to do that.
>
> Genealogy is one example. It has lots of dates MySQL won't accept.
> First, some people (such as English royalty) can trace their ancestry
> back before 1000 A.D., before the limit on the DATE type.
ACK.
> Second, MySQL doesn't like imprecise dates, such as only a year being
> known.
This can be solved by setting the unknown parts to 1 and have other fields
of the record specify the precision.
Also, there is the YEAR type (preferably used as YEAR(4)), but it only has a
range from 1901 to 2155:
<http://dev.mysql.com/doc/refman/5.6/en/year.html>
> Third, during a time when both the Julian and Gregorian calendars
> were in use, depending on location, you often see dates recorded
> as "5 January 1712/13" or "5 January 1712/3". You might also see
> "5 January 1712".
Same there.
> Since the Julian calendar as used in the English colonies that later
> became the United States used March 25 as the first day of the year,
> you can get ridiculous-looking records such as a child born: March
> 27, 1700, died: March 23, 1700 (not a mistake: the child lived
> almost a full year) and born: March 24, 1700, died: March 25, 1701
> (the child lived about 24 hours). Also, a date recorded as "the
> 6th day of the third month of 1699" might be referring to March or
> May.
>
> It may be best to treat all dates (birth, death, marriage, graduation,
> etc.) as ranges.
Depends.
> These should be DATE types for efficient sorting and date arithmetic.
That contradicts your premise that dates could be before 1000-01-01 CE. It
is usually the dates of birth and death of people born before that date that
are uncertain and require ranges. For example, Plato lived “[from] 428/427
or 424/423 BC[a] [to] 348/347 BC” (Wikipedia).
> It's also important to record the date(s) as stated in the source(s) as
> originally recorded, complete with accurate reproduction of spelling
> errors and in the original language, in text fields (or perhaps as an
> image). This is not redundant. You may have to do considerable research
> to set the DATE fields from the original source data that may be
> conflicting, imprecise, or ambiguous.
ACK. But that information should be stored in separate columns.
> You might need to try extening the Gregorian calendar backwards to avoid
> death before birth and other anomalies.
It has been done before. MySQL already does it, not least because the first
day of the Gregorian calendar is _not_ 0001-01-01 CE, but 1582-10-15 CE.
< https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calend ar>
This has nothing to do with PHP anymore. Please stop crossposting (without
F'up2).
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(at)94(dot)75(dot)214(dot)39>
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Storing dates [message #186235 is a reply to message #186229] |
Sat, 21 June 2014 15:10 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Sat, 21 Jun 2014 12:15:04 +0200, Thomas 'PointedEars' Lahn wrote:
> [F'up2 comp.databases.mysql
>
> Gordon Burditt wrote in comp.lang.php and comp.databases.mysql:
>
>> [“richard” wrote:]
>>> Why would anyone store a date in a MySQL
>>> database using any data type other than DATE or DATETIME?
>>
>> Believe it or not, there are (very rarely) good reasons to do that.
>>
>> Genealogy is one example. It has lots of dates MySQL won't accept.
>> First, some people (such as English royalty) can trace their ancestry
>> back before 1000 A.D., before the limit on the DATE type.
>
> ACK.
>
>> Second, MySQL doesn't like imprecise dates, such as only a year being
>> known.
>
> This can be solved by setting the unknown parts to 1 and have other fields
> of the record specify the precision.
>
> Also, there is the YEAR type (preferably used as YEAR(4)), but it only has a
> range from 1901 to 2155:
>
> <http://dev.mysql.com/doc/refman/5.6/en/year.html>
>
>> Third, during a time when both the Julian and Gregorian calendars
>> were in use, depending on location, you often see dates recorded
>> as "5 January 1712/13" or "5 January 1712/3". You might also see
>> "5 January 1712".
>
> Same there.
>
>> Since the Julian calendar as used in the English colonies that later
>> became the United States used March 25 as the first day of the year,
>> you can get ridiculous-looking records such as a child born: March
>> 27, 1700, died: March 23, 1700 (not a mistake: the child lived
>> almost a full year) and born: March 24, 1700, died: March 25, 1701
>> (the child lived about 24 hours). Also, a date recorded as "the
>> 6th day of the third month of 1699" might be referring to March or
>> May.
>>
>> It may be best to treat all dates (birth, death, marriage, graduation,
>> etc.) as ranges.
>
> Depends.
>
>> These should be DATE types for efficient sorting and date arithmetic.
>
> That contradicts your premise that dates could be before 1000-01-01 CE. It
> is usually the dates of birth and death of people born before that date that
> are uncertain and require ranges. For example, Plato lived “[from] 428/427
> or 424/423 BC[a] [to] 348/347 BC” (Wikipedia).
>
>> It's also important to record the date(s) as stated in the source(s) as
>> originally recorded, complete with accurate reproduction of spelling
>> errors and in the original language, in text fields (or perhaps as an
>> image). This is not redundant. You may have to do considerable research
>> to set the DATE fields from the original source data that may be
>> conflicting, imprecise, or ambiguous.
>
> ACK. But that information should be stored in separate columns.
>
>> You might need to try extening the Gregorian calendar backwards to avoid
>> death before birth and other anomalies.
>
> It has been done before. MySQL already does it, not least because the first
> day of the Gregorian calendar is _not_ 0001-01-01 CE, but 1582-10-15 CE.
>
> < https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calend ar>
>
> This has nothing to do with PHP anymore. Please stop crossposting (without
> F'up2).
>
>
> PointedEars
This is really getting hilarious.
You guys are bashing each other over the "proper use" of dates.
One says one thing, someone else comes along and say you're wrong.
Then gives a long winded diatribe as to why.
Ok smart people, try this one on for size.
A person is born on Ocober 4, 1582 (Julian) and dies on October
16,1582(Gregorian).
How long did he live?
Did you know that the monthly calendar as we know it today was actually
invented by the Romans? Not Pope Gregory?
Originally, the calendar had 10 months.
Each month named with Latin based numbering system.
Ergo, December being the tenth month.
October, the eighth month.
Who do the monhts July and August honor?
Julias Augustus Ceasar.
January and February were added by Numa Pompilius in 713 BC.
Februa is an ancient purification ritual.
As for the date usage on MY database tables, I am only using a date as a
reference. I really don't give a damn what your philosophy or ideals are
behind the so called "proper ways" of obtaining a date.
|
|
|
|
|
|
|
|
|
|
| Re: Storing dates [message #186245 is a reply to message #186244] |
Sat, 21 June 2014 19:04 |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
On Sat, 21 Jun 2014 14:28:47 -0400, richard <noreply(at)example(dot)com> wrote
in comp.databases.mysql:
> On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>
>> richard <noreply(at)example(dot)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
|
|
|
|
| Re: Storing dates [message #186246 is a reply to message #186244] |
Sat, 21 June 2014 19:06 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
richard <noreply(at)example(dot)com> wrote in
news:hzk4wa449wa$(dot)3xtj0jkxz78o$(dot)dlg(at)40tude(dot)net:
> On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>
>> richard <noreply(at)example(dot)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.
|
|
|
|
| Re: Storing dates [message #186249 is a reply to message #186246] |
Sat, 21 June 2014 21:06 |
Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
In article <XnsA353998B95688dougmilmaccom(at)78(dot)46(dot)70(dot)116>, Doug Miller
<doug_at_milmac_dot_com(at)example(dot)com> wrote:
> richard <noreply(at)example(dot)com> wrote in
> news:hzk4wa449wa$(dot)3xtj0jkxz78o$(dot)dlg(at)40tude(dot)net:
>
>> On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>>
>>> richard <noreply(at)example(dot)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
|
|
|
|
| Re: Storing dates [message #186250 is a reply to message #186249] |
Sat, 21 June 2014 21:09 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/21/2014 5:06 PM, Tim Streater wrote:
> In article <XnsA353998B95688dougmilmaccom(at)78(dot)46(dot)70(dot)116>, Doug Miller
> <doug_at_milmac_dot_com(at)example(dot)com> wrote:
>
>> richard <noreply(at)example(dot)com> wrote in
>> news:hzk4wa449wa$(dot)3xtj0jkxz78o$(dot)dlg(at)40tude(dot)net:
>>> On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>>>> > richard <noreply(at)example(dot)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(at)attglobal(dot)net
==================
|
|
|
|
|
|
| Re: Storing dates [message #186252 is a reply to message #186250] |
Sat, 21 June 2014 21:33 |
Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
In article <lo4sa4$h1p$1(at)dont-email(dot)me>, Jerry Stuckle
<jstucklex(at)attglobal(dot)net> wrote:
> On 6/21/2014 5:06 PM, Tim Streater wrote:
>> In article <XnsA353998B95688dougmilmaccom(at)78(dot)46(dot)70(dot)116>, Doug Miller
>> <doug_at_milmac_dot_com(at)example(dot)com> wrote:
>>
>>> richard <noreply(at)example(dot)com> wrote in
>>> news:hzk4wa449wa$(dot)3xtj0jkxz78o$(dot)dlg(at)40tude(dot)net:
>>>> On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>>>> >> richard <noreply(at)example(dot)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
|
|
|
|
| Re: Storing dates [message #186253 is a reply to message #186251] |
Sat, 21 June 2014 21:44 |
Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
In article <d3ubq91a9ufstpi1fp963r54684sue6cr9(at)4ax(dot)com>, Jürgen Exner
<jurgenex(at)hotmail(dot)com> wrote:
> On Sat, 21 Jun 2014 22:06:25 +0100, Tim Streater
> <timstreater(at)greenbee(dot)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
|
|
|
|
| Re: Storing dates [message #186254 is a reply to message #186252] |
Sat, 21 June 2014 21:49 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/21/2014 5:33 PM, Tim Streater wrote:
> In article <lo4sa4$h1p$1(at)dont-email(dot)me>, Jerry Stuckle
> <jstucklex(at)attglobal(dot)net> wrote:
>
>> On 6/21/2014 5:06 PM, Tim Streater wrote:
>>> In article <XnsA353998B95688dougmilmaccom(at)78(dot)46(dot)70(dot)116>, Doug Miller
>>> <doug_at_milmac_dot_com(at)example(dot)com> wrote:
>>>> > richard <noreply(at)example(dot)com> wrote in
>>>> news:hzk4wa449wa$(dot)3xtj0jkxz78o$(dot)dlg(at)40tude(dot)net:
>>>> > On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>>>> > >> richard <noreply(at)example(dot)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(at)attglobal(dot)net
==================
|
|
|
|
|
|
|
|
| Re: Storing dates [message #186261 is a reply to message #186244] |
Sun, 22 June 2014 06:53 |
Arno Welzel
Messages: 317
Registered: October 2011
Karma: 0
|
Senior Member |
|
|
richard, 2014-06-21 20:28:
> On Sat, 21 Jun 2014 17:42:29 +0000 (UTC), Doug Miller wrote:
>
>> richard <noreply(at)example(dot)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
|
|
|
|
|
|
| Re: Storing dates [message #186263 is a reply to message #186252] |
Sun, 22 June 2014 12:38 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
[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/SQLiteOpenHe lper.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(at)94(dot)75(dot)214(dot)39>
|
|
|
|
| Re: Storing dates [message #186264 is a reply to message #186251] |
Sun, 22 June 2014 12:59 |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
[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(at)greenbee(dot)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(at)94(dot)75(dot)214(dot)39>
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]