Обсуждение: "order by" days of the week

Hello,

I am storing a schedule of services in a restaurant database (e.g.
breakfast, lunch...), and want to be able to order the results by the day
of the week. The table is as follows.

                Table "schedule"
   Column    |          Type          | Modifiers
-------------+------------------------+-----------
 business_id | integer                | not null
 day         | character varying(20)  |
 start       | time without time zone |
 finish      | time without time zone |
 service     | character varying(30)  |

The day collumn will hold the days of the week "Monday", "Tuesday"... etc.
When I retrieve the data, I want to be able to sort by the days in
cronological order rather than in alphabetic order.

My questions are these:
1) Is it possible to use "order by" in conjunction with a list of strings
to tell it how to order the results?

2)Is there a way to store days of the week that is better than simply
using varchar and performing a check. I read about the time data formats
and they all seem to be more applicable to absolute times rather than
simply a day of the week. I also thought about storing the days as a
number between 1 and 7 then converting for display (1=Monday,
2=Tuesday...), but that didn't feel clean to me.

Any suggestions?

Thanks,
Doug

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

On Sat, 2002-09-28 at 02:36, D. A. wrote:
> I am storing a schedule of services in a restaurant database (e.g.
> breakfast, lunch...), and want to be able to order the results by the day
> of the week. The table is as follows.
>
>                 Table "schedule"
>    Column    |          Type          | Modifiers
> -------------+------------------------+-----------
>  business_id | integer                | not null
>  day         | character varying(20)  |
>  start       | time without time zone |
>  finish      | time without time zone |
>  service     | character varying(30)  |
>
> The day collumn will hold the days of the week "Monday", "Tuesday"... etc.
> When I retrieve the data, I want to be able to sort by the days in
> cronological order rather than in alphabetic order.
>
> My questions are these:
> 1) Is it possible to use "order by" in conjunction with a list of strings
> to tell it how to order the results?

You could write a function to convert day names into numbers and sort on
the output of that function.

> 2)Is there a way to store days of the week that is better than simply
> using varchar and performing a check. I read about the time data formats
> and they all seem to be more applicable to absolute times rather than
> simply a day of the week. I also thought about storing the days as a
> number between 1 and 7 then converting for display (1=Monday,
> 2=Tuesday...), but that didn't feel clean to me.

It seems cleaner to me.  It would take less storage and be easier to
validate ("CONSTRAINT "valid day" CHECK (day >= 0 AND day < 7)").
Create a view that has an additional dayname column defined as "CASE
WHEN day = 0 THEN "Sunday" WHEN day = 1 THEN "Monday" ... END"  Then you
can sort by day but select dayname.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Blessed is the man that endureth temptation; for when
      he is tried, he shall receive the crown of life, which
      the Lord hath promised to them that love him."
                                          James 1:12

>>>>> "D" == D A <lameije@yahoo.com> writes:

    D> 2)Is there a way to store days of the week that is better than simply
    D> using varchar and performing a check. I read about the time data formats
    D> and they all seem to be more applicable to absolute times rather than
    D> simply a day of the week. I also thought about storing the days as a
    D> number between 1 and 7 then converting for display (1=Monday,
    D> 2=Tuesday...), but that didn't feel clean to me.

The was I've seen this done is to have an extra lookup table where
1=Monday does the translation between the key (the value 1) and the
human-readable version (the value "Monday").  I've not seen it for
this particular case (days of the week), but the idea is quite
general and works fine for this case.

roland
--
               PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375