Re: "order by" days of the week
От | Oliver Elphick |
---|---|
Тема | Re: "order by" days of the week |
Дата | |
Msg-id | 1033244644.7980.465.camel@linda обсуждение исходный текст |
Ответ на | "order by" days of the week ("D. A." <lameije@yahoo.com>) |
Список | 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.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
В списке pgsql-novice по дате отправления: