I have the following database:
create workers (
id SERIAL PRIMARY KEY,
name STRING
);
create works_zones (
id SERIAL PRIMARY KEY,
area_name String,
work_from TIME WITHOUT TIMEZONE,
work_until TIME WITHOUT TIMEZONE
)
create table shifts (
id SERIAL PRIMARY KEY,
worker_id INT,
day_of_week INT,
work_zone INT,
CONSTRAINT fk_worker FOREIGN KEY(worker_id) REFERENCES workers(id) ON DELETE CASCADE,
CONSTRAINT fk_work_zone FOREIGN KEY(work_zone) REFERENCES works_zones(id) ON DELETE CASCADE
)
CREATE UNIQUE INDEX CONCURRENTLY unique_day_zone_worker ON shifts (worker_id,work_zone,"day_of_week");
And I try to list all worker shifts:
select
workers.name,
works_zones.area_name,
works_zones.work_from,
works_zones.work_until,
shifts.day_of_week
from
shifts
join
workers on shifts.worker_id = workers.id
join
works_zones on works_zones.id = shifts.work_zone
But how I can translate the day_of_week
into human readable form and expecially localized into Greek? The values are ranging from 0 to 6 where ) is Sunday
and 6 is Saturday
.
Is there a way to do is without the use of Case...when .. end
?