| Re: How to loop through the dates? [message #186162 is a reply to message #186145] |
Tue, 17 June 2014 06:18   |
Arno Welzel
Messages: 317
Registered: October 2011
Karma:
|
Senior Member |
|
|
richard, 2014-06-16 18:26:
> On Sun, 15 Jun 2014 19:14:22 -0400, Lew Pitcher wrote:
>
>> On Sunday 15 June 2014 13:40, in comp.lang.php, "Denis McMahon"
>> <denismfmcmahon(at)gmail(dot)com> wrote:
>>
>>> On Sun, 15 Jun 2014 12:11:01 -0400, richard wrote:
>>>
>>>> Data is ordered by date.
>>>
>>> See, this is where the richard database design starts to bite the richard
>>> arse.
>>>
>>> You are storing dates as a string representation of the date, so your
>>> "order by date" sql clause causes the data to be ordered according to the
>>> string collation for the relevant table.
>>>
>>> If you want the mysql rdbms to sort the data into date sequence, then you
>>> need to store the data as dates.
>>
>> That's good advice (the best).
>>
>> However, richard can get away with
>> SELECT * FROM table ORDER BY STR_TO_DATE(date,date_string_format);
>>
>> That will convert his string dates to "real" dates for the purposes of
>> ordering the resultset rows, and should come out in ascending sequence by
>> calendar date.
>> http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#functio n_str-to-date
>>
>>
>>
>> [snip]
>
> What is it with you people over this damn date issue?
Because YOU said in your initial posting, that you use THIS statement:
SELECT * FROM tracker ORDER BY date
Now - if "date" is not a date type but just a text field, the ORDER BY
clause will NOT work as intended:
01-07-1996
02-03-2010
10-10-1985
12-11-2014
You see - the order seems to be quite "random" - 1996, then 2010, then
1985 and finally 2014.
This is caused by not using the date type for the column "date" - so the
dates will just be treated as text and therefore ordered alphabetically
but not by their date values.
> I am only using date in the table as a unique identifier.
No - you are also using the date to order stuff as shown in your first post.
But I guess you even don't know what "ORDER BY" means nor what the
concept of a "data type" is.
--
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]