Professional Web Applications Themes

sorting by the closest date - MySQL

Hello! I have got a tabale with dates: 2006-12-01 2007-01-12 2006-12-31 2008-01-05 2006-12-08 I want to display ALL dates, but sort this way, that the closest dates are on top, next there are future dates, and at the end are past dates: 2007-01-12 2008-01-05 2006-12-31 2006-12-08 2006-12-01 How do this? Should I create temporary table and load dates with my conditons or do something else? Does solution with temporary table is efficient?...

  1. #1

    Default sorting by the closest date

    Hello!
    I have got a tabale with dates:
    2006-12-01
    2007-01-12
    2006-12-31
    2008-01-05
    2006-12-08


    I want to display ALL dates, but sort this way, that the closest dates
    are on top, next there are future dates, and at the end are past dates:
    2007-01-12
    2008-01-05
    2006-12-31
    2006-12-08
    2006-12-01

    How do this? Should I create temporary table and load dates with my
    conditons or do something else?
    Does solution with temporary table is efficient?

    krzys Guest

  2. #2

    Default Re: sorting by the closest date

    krzys wrote: 


    surely "closest" dates are also future dates??

    And is today a closest date, a past date or a future date?

    Assuming that the date field is called date_field and the table is called
    date_table, try:

    SELECT date_field FROM date_table WHERE date_field > NOW() ORDER BY
    date_field
    UNION
    SELECT date_field FROM date_table WHERE date_field <= NOW() ORDER BY
    date_field DESC

    you may need to play around with the where clause depending on the answer to
    one of above questions.


    Paul Guest

  3. #3

    Default Re: sorting by the closest date

    Paul Lautman wrote:
     
    >
    >
    > surely "closest" dates are also future dates??
    >
    > And is today a closest date, a past date or a future date?
    >
    > Assuming that the date field is called date_field and the table is called
    > date_table, try:
    >
    > SELECT date_field FROM date_table WHERE date_field > NOW() ORDER BY
    > date_field
    > UNION
    > SELECT date_field FROM date_table WHERE date_field <= NOW() ORDER BY
    > date_field DESC[/ref]


    That's what I tried and got:

    ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  4. #4

    Default Re: sorting by the closest date

    krzys wrote:
     


    First, it would be interesting to note the business case for needing data
    presented in this manner - in generic terms of course... no need for details...

    ORDER BY - which uses a simple sort-routine can only do ascending or decending -
    not mix-cending :). If there are secondary keys, you could get closer,
    depending on that key value - such as another date. Even that will still only
    do ascending/descending.

    If these dates could in some way be transformed into Spatial data, then you
    would have a way to find "points" that are further and further away from the
    starting point. This is used on some sites to find "stores" near your current
    zip code and can order them based on distance.

    The link below would appear to be an excellent starting point - you will need to
    think WAY outside the box. Don't forget - in your spatial data table, you would
    need a column that is the "date" that can be joined to the other table.

    http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

    This may help you get started...
    mysql> describe mm;
    +-------+-----------+------+-----+-------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+-----------+------+-----+-------------------+-------+
    | a | timestamp | YES | | CURRENT_TIMESTAMP | |
    | b | date | YES | | NULL | |
    | c | time | YES | | NULL | |
    +-------+-----------+------+-----+-------------------+-------+
    3 rows in set (3.93 sec)

    mysql> insert into mm values (current_timestamp,current_date,current_time);
    Query OK, 1 row affected (0.21 sec)

    mysql> select * from mm;
    +---------------------+------------+----------+
    | a | b | c |
    +---------------------+------------+----------+
    | 2007-01-06 12:30:04 | 2007-01-06 | 12:30:04 |
    +---------------------+------------+----------+
    1 row in set (0.01 sec)

    mysql> select UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b), UNIX_TIMESTAMP(FROM_UNIXTIME
    (c)) from mm;
    +-------------------+-------------------+----------------------------------+
    | UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | UNIX_TIMESTAMP(FROM_UNIXTIME(c)) |
    +-------------------+-------------------+----------------------------------+
    | 1168108204 | 1168063200 | 123004 |
    +-------------------+-------------------+----------------------------------+
    1 row in set (0.02 sec)

    If stored "spatially", then you can calculate "distance" between two dates and
    sort by that "distance" - but of course, you would then only display the dates.

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  5. #5

    Default Re: sorting by the closest date

    Michael Austin wrote: 
    >
    >
    > First, it would be interesting to note the business case for needing
    > data presented in this manner - in generic terms of course... no need
    > for details...
    > ORDER BY - which uses a simple sort-routine can only do ascending or
    > decending - not mix-cending :). If there are secondary keys, you
    > could get closer, depending on that key value - such as another date.
    > Even that will still only do ascending/descending.
    >
    > If these dates could in some way be transformed into Spatial data,
    > then you would have a way to find "points" that are further and
    > further away from the starting point. This is used on some sites to
    > find "stores" near your current zip code and can order them based on
    > distance.
    > The link below would appear to be an excellent starting point - you
    > will need to think WAY outside the box. Don't forget - in your
    > spatial data table, you would need a column that is the "date" that
    > can be joined to the other table.
    > http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html
    >
    > This may help you get started...
    > mysql> describe mm;
    > +-------+-----------+------+-----+-------------------+-------+ 
    > +-------+-----------+------+-----+-------------------+-------+ 
    > +-------+-----------+------+-----+-------------------+-------+
    > 3 rows in set (3.93 sec)
    >
    > mysql> insert into mm values
    > (current_timestamp,current_date,current_time); Query OK, 1 row
    > affected (0.21 sec)
    > mysql> select * from mm;
    > +---------------------+------------+----------+ 
    > +---------------------+------------+----------+ 
    > +---------------------+------------+----------+
    > 1 row in set (0.01 sec)
    >
    > mysql> select UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b),
    > UNIX_TIMESTAMP(FROM_UNIXTIME (c)) from mm;
    > +-------------------+-------------------+----------------------------------+ 
    > +-------------------+-------------------+----------------------------------+ 
    > +-------------------+-------------------+----------------------------------+
    > 1 row in set (0.02 sec)
    >
    > If stored "spatially", then you can calculate "distance" between two
    > dates and sort by that "distance" - but of course, you would then
    > only display the dates.[/ref]

    Interesting, but isn't the UNION approach rather easier?


    Paul Guest

  6. #6

    Default Re: sorting by the closest date

    Paul Lautman wrote:
     [/ref][/ref]
    [snipped for brevity]

    That will not get what he wants... He said he want dates that are "closest" on
    top. With "closest" in the picture, you cannot use the UNION method you described.

    given today is 6-Jan-2007:

    1-dec-2006
    31-dec-2006
    5-Jan-2007
    7-jan-2007
    1-dec-2007

    Which is closer? 5-Jan-2007 or 7-Jan-2007. is there an actual full timestamp or
    just a date? The answer is why you want the data returned in this method and
    can future dates be cloesest dates - if no - then the union method also does not
    work per se, due the fact that you cannot order by in a union.... But you can
    create views...

    mysql> create view cl1 as select * from mm where b = (select max(b) from mm wher
    e b < NOW()) order by b;
    Query OK, 0 rows affected (3.72 sec)

    mysql> create view cl2 as select * from mm where b > NOW() order by b;
    Query OK, 0 rows affected (3.44 sec)

    mysql> create view cl3 as select * from mm where b < (select max(b) from mm wher
    e b < NOW()) order by b; // we already have the MAX(b) less than NOW().
    Query OK, 0 rows affected (3.43 sec)

    mysql> create view c4 as select * from cl1 union select * from cl2 union select
    * from cl3;
    Query OK, 0 rows affected (3.67 sec)

    mysql> select * from c4
    -> ;
    +---------------------+------------+------+
    | a | b | c |
    +---------------------+------------+------+
    | 2007-01-06 15:01:47 | 2006-12-31 | NULL |
    | 2007-01-06 15:00:37 | 2007-01-12 | NULL |
    | 2007-01-06 15:01:47 | 2007-01-12 | NULL |
    | 2007-01-06 15:01:47 | 2008-01-05 | NULL |
    | 2007-01-06 15:01:47 | 2006-12-01 | NULL |
    | 2007-01-06 15:01:47 | 2006-12-08 | NULL |
    +---------------------+------------+------+
    6 rows in set (0.22 sec)


    If closest is future OR past, then the Spatial route is the only way that will
    work 100% of the time. Again, goes to the business case for wanting data
    returned in this method in the first place...

    --
    Michael Austin.
    Database Consultant
    Michael Guest

Similar Threads

  1. Finding a date closest to a specific date
    By Explorer5 in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 15th, 02:03 PM
  2. CFPOP sorting by date
    By bigreddastud in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 23rd, 10:05 PM
  3. Querying a date closest to today
    By Explorer5 in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 24th, 07:53 PM
  4. Date Sorting
    By Greg in forum PHP Development
    Replies: 4
    Last Post: October 1st, 03:22 PM
  5. date sorting module
    By Sam in forum PERL Miscellaneous
    Replies: 3
    Last Post: September 16th, 04:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139