csanyipal@gmail.com wrote:
>>> I have a table with columns: number, date-range.
>>> I wish to get in this table more columns: week-day-begin, week-day-end.
>>>
>>> In the week-day-begin column I wish to get automatically the weekday
>>> name of the first date in the date range.
>>>
>>> In the week-day-end column I wish to get automatically the weekday
>>> name of the last date in the date range.
>> CREATE TABLE test (i integer PRIMARY KEY, dr daterange NOT NULL);
>>
>> CREATE VIEW testview AS
>> SELECT i, dr,
>> EXTRACT(DOW FROM lower(dr)) AS wd_start,
>> EXTRACT(DOW FROM upper(dr)) AS wd_end
>> FROM test;
>>
>> INSERT INTO test VALUES (1, '[1968-10-20,2050-04-01]');
>>
>> SELECT * FROM testview;
>>
>> i | dr | wd_start | wd_end
>> ---+-------------------------+----------+--------
>> 1 | [1968-10-20,2050-04-02) | 0 | 6
>> (1 row)
> Thanks, but this gives no names of weekdays but just numbers.
>
> How can I get the name ( eg. Monday ) of the weekday in this view?
Try something like a CASE clause:
CASE EXTRACT(DOW FROM lower(dr)) WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' ... END
Yours,
Laurenz Albe