| 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 #186211 is a reply to message #186209] |
Fri, 20 June 2014 15:21  |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com> wrote
in comp.databases.mysql:
> http://mroldies.net/radio/tracker4.php
> For this excercise, I decided to order by artist.
> I'll do it by date later.
Does this have anything to do with SQL?
> 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.
There is a SQL function stripslashes()? I didn't know that.
Or did you mean TRIM()?
jue
|
|
|
|
| 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 #186217 is a reply to message #186209] |
Sat, 21 June 2014 01:06 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 06/20/2014 10:20 AM, richard wrote:
> 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.
>
#1 - mysqli_real_escape_string();
#2 - ORDER BY artist, title ASC
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
|
|
|
|
| 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 #186220 is a reply to message #186209] |
Sat, 21 June 2014 03:09 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 10:20:59 -0400, richard wrote:
> http://mroldies.net/radio/tracker4.php
I only found the following errors, but I only had a quick look, so there
may be more:
1) & character not sent as html entity
2) backslash character in the band name "Herman's Hermits"
So it looks like you need to visit that drawing board again.
Note that there are special functions in certain programming languages to
(a) automatically insert html entities into text as needed, and (b)
automatically add backslashes into text being inserted into mysql
databases as needed. If you used these functions correctly, some of your
problems might be solved. Other problems, well it looks like being richard
isn't fixable.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Putting it all together [message #186221 is a reply to message #186209] |
Sat, 21 June 2014 03:13 |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com> wrote
in comp.databases.mysql:
> For this excercise, I decided to order by artist.
> I'll do it by date later.
That should be as simple as
SELECT .... FROM ...... ORDER BY date
Of course that assumes that date is of type date (or datetime) as any
sane person would store a date.
jue
|
|
|
|
| Re: Putting it all together [message #186222 is a reply to message #186221] |
Sat, 21 June 2014 03:34 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 20:13:10 -0700, Jürgen Exner wrote:
> On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com> wrote
> in comp.databases.mysql:
>
>> For this excercise, I decided to order by artist.
>> I'll do it by date later.
>
> That should be as simple as
> SELECT .... FROM ...... ORDER BY date
>
> Of course that assumes that date is of type date (or datetime) as any
> sane person would store a date.
>
> jue
what if use Julian dates?
that throws all of your damn bullshit right down the drain don't it?
|
|
|
|
| Re: Putting it all together [message #186223 is a reply to message #186222] |
Sat, 21 June 2014 03:48 |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
On Fri, 20 Jun 2014 23:34:19 -0400, richard <noreply(at)example(dot)com> wrote
in comp.databases.mysql:
> On Fri, 20 Jun 2014 20:13:10 -0700, Jürgen Exner wrote:
>
>> On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com> wrote
>> in comp.databases.mysql:
>>
>>> For this excercise, I decided to order by artist.
>>> I'll do it by date later.
>>
>> That should be as simple as
>> SELECT .... FROM ...... ORDER BY date
>>
>> Of course that assumes that date is of type date (or datetime) as any
>> sane person would store a date.
>
> what if use Julian dates?
> that throws all of your damn bullshit right down the drain don't it?
If done correctly it is still as simple as
SELECT .... FROM ...... ORDER BY date
Of course you have the additional trouble of converting back and forth
between Julian and Gregorian date and time because there aren't many
people who could interpret a Julian date, but that can certainly be
solved.
jue
|
|
|
|
| Re: Putting it all together [message #186224 is a reply to message #186222] |
Sat, 21 June 2014 04:00 |
Evan Platt
Messages: 124
Registered: November 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 23:34:19 -0400, richard <noreply(at)example(dot)com>
wrote:
> what if use Julian dates?
> that throws all of your damn bullshit right down the drain don't it?
Wow, and you wonder why no one wants to help you.
--
To reply via e-mail, remove The Obvious and .invalid from my e-mail address.
|
|
|
|
| Re: Putting it all together [message #186225 is a reply to message #186222] |
Sat, 21 June 2014 04:22 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
richard <noreply(at)example(dot)com> wrote in news:1v5cqtux7fm2t$.14sqgdhtzf3vw.dlg@
40tude.net:
> On Fri, 20 Jun 2014 20:13:10 -0700, Jrgen Exner wrote:
>
>> On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com> wrote
>> in comp.databases.mysql:
>>
>>> For this excercise, I decided to order by artist.
>>> I'll do it by date later.
>>
>> That should be as simple as
>> SELECT .... FROM ...... ORDER BY date
>>
>> Of course that assumes that date is of type date (or datetime) as any
>> sane person would store a date.
>>
>> jue
>
> what if use Julian dates?
Why on earth would you want to do *that*? Why would anyone store a date in a MySQL
database using any data type other than DATE or DATETIME?
> that throws all of your damn bullshit right down the drain don't it?
There's one *more* thing you need to apologize for, Bullis.
And you wonder why nobody wants to help you. How much longer will it be before you
grasp the concept that the *external* *presentation* of date or date-time data can (and
usually *should*) be different from the *internal* format in which it is stored?
|
|
|
|
| Re: Putting it all together [message #186226 is a reply to message #186225] |
Sat, 21 June 2014 05:11 |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
On Sat, 21 Jun 2014 04:22:43 +0000 (UTC), Doug Miller
<doug_at_milmac_dot_com(at)example(dot)com> wrote in comp.databases.mysql:
> richard <noreply(at)example(dot)com> wrote in news:1v5cqtux7fm2t$.14sqgdhtzf3vw.dlg@
> 40tude.net:
>> what if use Julian dates?
>
> How much longer will it be before you
> grasp the concept that the *external* *presentation* of date or date-time data can (and
> usually *should*) be different from the *internal* format in which it is stored?
Well, in this case here I *SERIOUSLY* hope that he does not present
Julian dates to the user, even if that is his internal format.
jue
|
|
|
|
| Re: Putting it all together [message #186227 is a reply to message #186226] |
Sat, 21 June 2014 05:39 |
Evan Platt
Messages: 124
Registered: November 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 22:11:53 -0700, Jürgen Exner
<jurgenex(at)hotmail(dot)com> wrote:
> Well, in this case here I *SERIOUSLY* hope that he does not present
> Julian dates to the user, even if that is his internal format.
Unix time is the way to go....
--
To reply via e-mail, remove The Obvious and .invalid from my e-mail address.
|
|
|
|
| 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: Putting it all together [message #186230 is a reply to message #186222] |
Sat, 21 June 2014 10:23 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 23:34:19 -0400, richard wrote:
> what if use Julian dates?
> that throws all of your damn bullshit right down the drain don't it?
No, it doesn't change anything. As long as you store the dates using an
appropriate datatype for a date, you can sort by date.
Whether you call it date and store a datetime, or call it date and store
a julian date as a number from some arbitrary epoch is irrelevant.
Your mistake is in storing dates as strings and then expecting string
sort to sort them in date order.
You've been given a workround for this at least twice which would only
need you to wrap the column name date in your order by clause with a
suitable conversion function.
If instead you want to make more mistakes with julian dates, well who are
we to stand in your way. But when you start asking for help in sorting
out your julian dates, I reserve the non exclusive right to tell tell you
to see previous discussions and stop being an arse.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: Putting it all together [message #186231 is a reply to message #186228] |
Sat, 21 June 2014 10:34 |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
On Sat, 21 Jun 2014 04:15:04 -0500, gordonb(dot)zp2md(at)burditt(dot)org (Gordon
Burditt) wrote in comp.databases.mysql:
>> Why would anyone store a date in a MySQL
>> database using any data type other than DATE or DATETIME?
>
> Since the Julian calendar [...]
Careful!
The Julian calender is something very different from a Julian day.
Now, having said that, the OP used the term "Julian date", and it is not
absoluty clear if he meant a day in the Julian calender or a Julian day.
But I interpret it as a Julian day, because a day in the Julian calender
makes even less sense.
jue
|
|
|
|
| Re: Putting it all together [message #186232 is a reply to message #186222] |
Sat, 21 June 2014 11:03 |
bill
Messages: 310
Registered: October 2010
Karma: 0
|
Senior Member |
|
|
On 6/20/2014 11:34 PM, richard wrote:
>
> what if use Julian dates?
> that throws all of your damn bullshit right down the drain don't it?
>
Actually using an ISO date would be better
|
|
|
|
| Re: Putting it all together [message #186234 is a reply to message #186232] |
Sat, 21 June 2014 15:11 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Sat, 21 Jun 2014 07:03:04 -0400, bill wrote:
> On 6/20/2014 11:34 PM, richard wrote:
>> what if use Julian dates?
>> that throws all of your damn bullshit right down the drain don't it?
> Actually using an ISO date would be better
Actually, in richard's case, forgetting about databases and using csv
text files instead would be better. And some sort of log file for his hit
tracker.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| 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: Putting it all together [message #186237 is a reply to message #186222] |
Sat, 21 June 2014 15:57 |
Peter H. Coffin
Messages: 245
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Fri, 20 Jun 2014 23:34:19 -0400, richard wrote:
> On Fri, 20 Jun 2014 20:13:10 -0700, Jürgen Exner wrote:
>
>> On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com>
>> wrote in comp.databases.mysql:
>>
>>> For this excercise, I decided to order by artist I'll do it by date .
>>> later .
>>
>> That should be as simple as SELECT .... FROM ...... ORDER BY date
>>
>> Of course that assumes that date is of type date (or datetime) as any
>> sane person would store a date.
>>
>> jue
>
> what if use Julian dates that throws all of your damn bullshit right ?
> down the drain don't it ?
I'll write it for you when you come up with an audio recording made
before October 1582.
--
"... I've seen Sun monitors on fire off the side of the multimedia lab.
I've seen NTU lights glitter in the dark near the Mail Gate.
All these things will be lost in time, like the root partition last
week. Time to die...". -- Peter Gutmann in the scary.devil.monastery
|
|
|
|
| Re: Storing dates [message #186241 is a reply to message #186235] |
Sat, 21 June 2014 17:42 |
Doug Miller
Messages: 171
Registered: August 2011
Karma: 0
|
Senior Member |
|
|
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.
|
|
|
|
| Re: Storing dates [message #186244 is a reply to message #186241] |
Sat, 21 June 2014 18:28 |
Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
|
Senior Member |
|
|
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
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
|
|
|
|
| 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 #186251 is a reply to message #186249] |
Sat, 21 June 2014 21:31 |
Jrgen Exner
Messages: 14
Registered: March 2013
Karma: 0
|
Junior Member |
|
|
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.
> 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
|
|
|
|
| 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 #186255 is a reply to message #186244] |
Sat, 21 June 2014 23:19 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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(at)gmail(dot)com
|
|
|
|
| Re: Putting it all together [message #186260 is a reply to message #186222] |
Sun, 22 June 2014 06:45 |
Arno Welzel
Messages: 317
Registered: October 2011
Karma: 0
|
Senior Member |
|
|
richard, 2014-06-21 05:34:
> On Fri, 20 Jun 2014 20:13:10 -0700, Jürgen Exner wrote:
>
>> On Fri, 20 Jun 2014 10:20:59 -0400, richard <noreply(at)example(dot)com> wrote
>> in comp.databases.mysql:
>>
>>> For this excercise, I decided to order by artist.
>>> I'll do it by date later.
>>
>> That should be as simple as
>> SELECT .... FROM ...... ORDER BY date
>>
>> Of course that assumes that date is of type date (or datetime) as any
>> sane person would store a date.
>>
>> jue
>
> what if use Julian dates?
Then use
TO_DAYS(date)+1721060
in MySQL.
Or vice versa
FROM_DAYS(date-1721060)
to convert from julian date.
> that throws all of your damn bullshit right down the drain don't it?
Nope.
--
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de
|
|
|
|
| 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
]