I try to make work schedule table.
I have a table like:
| shift_starts_dt | shift_type |
|---|---|
| 2022-01-01 08:00:00 | Day |
| 2022-01-01 20:00:00 | Night |
| 2022-01-02 08:00:00 | Day |
| 2022-01-02 20:00:00 | Night |
| 2022-01-03 08:00:00 | Day |
| 2022-01-03 20:00:00 | Night |
| 2022-01-04 08:00:00 | Day |
| 2022-01-04 20:00:00 | Night |
etc.. until the end of the year
I can't figure out how to add repeated values to table.
I want to add the 'shift_name' column that contains 'A','B','C','D' (It's like name for team)
What query should I use to achieve the next result:
| shift_starts_dt | shift_type | shift_name |
|---|---|---|
| 2022-01-01 08:00:00 | Day | 'A' |
| 2022-01-01 20:00:00 | Night | 'B' |
| 2022-01-02 08:00:00 | Day | 'C' |
| 2022-01-02 20:00:00 | Night | 'D' |
| 2022-01-03 08:00:00 | Day | 'A' |
| 2022-01-03 20:00:00 | Night | 'B' |
| 2022-01-04 08:00:00 | Day | 'C' |
| 2022-01-04 20:00:00 | Night | 'D' |
. . . . . .
CodePudding user response：
Use number of half days since Jan 1 modulus 4 to index an array:
select
shift_starts_dt,
shift_type,
(array['A','B','C','D'])[(extract(epoch from shift_starts_dt - '2022-01-01')::int / 43200) % 4 1]
from work_schedule
See 