Re: "order by" days of the week

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: "D(dot) A(dot)" <lameije(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: "order by" days of the week
Date: 2002-09-28 20:24:04
Message-ID: 1033244644.7980.465.camel@linda
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(dot)Elphick(at)lfix(dot)co(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Roland Roberts 2002-09-29 00:30:06 Re: "order by" days of the week
Previous Message Ludwig Lim 2002-09-28 07:28:50 Is this error message PostgreSQL related?