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

Groups > comp.lang.php > #13895

Re: How to loop through the dates?

Show all headers | View raw

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@gmail.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#function_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

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

How to loop through the dates? richard <noreply@example.com> - 2014-06-15 12:11 -0400
  Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 18:38 +0200
    Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 17:43 +0000
      Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 20:03 +0200
  Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 17:40 +0000
    Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-15 13:59 -0400
      Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 20:27 +0200
      Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 19:14 +0000
      Re: How to loop through the dates? Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-15 21:39 +0000
      Re: How to loop through the dates? Arno Welzel <usenet@arnowelzel.de> - 2014-06-17 08:12 +0200
    Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 20:01 +0200
      Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-15 19:07 +0000
        Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 21:58 +0200
          Re: How to loop through the dates? Denis McMahon <denismfmcmahon@gmail.com> - 2014-06-16 00:55 +0000
        Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-16 13:02 -0400
          Re: How to loop through the dates? Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-16 17:57 +0000
    Re: How to loop through the dates? Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2014-06-15 19:14 -0400
      Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-16 12:26 -0400
        Re: How to loop through the dates? Doug Miller <doug_at_milmac_dot_com@example.com> - 2014-06-16 16:39 +0000
        Re: How to loop through the dates? Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2014-06-16 14:14 -0400
        Re: How to loop through the dates? Arno Welzel <usenet@arnowelzel.de> - 2014-06-17 08:18 +0200
  Re: How to loop through the dates? Tim Streater <timstreater@greenbee.net> - 2014-06-15 20:30 +0100
    Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 22:01 +0200
      Re: How to loop through the dates? Tim Streater <timstreater@greenbee.net> - 2014-06-15 21:34 +0100
        Re: How to loop through the dates? Luuk <luuk@invalid.lan> - 2014-06-15 23:32 +0200
        Re: How to loop through the dates? richard <noreply@example.com> - 2014-06-16 12:19 -0400
          Re: How to loop through the dates? Norman Peelman <npeelman@cfl.rr.com> - 2014-06-16 20:44 -0400
  Re: How to loop through the dates? Norman Peelman <npeelman@cfl.rr.com> - 2014-06-15 20:59 -0400

csiph-web