Find shift based on a given date time in ORACLE/sql

Question

In a table X I have a date column that also returns the time, I wonder if, based on this time, I have how to create a column with the name of the turn. ex( if you have a die 20-02-2002 06:00:00 I want the char 'Morning' to appear in the column). any idea ?

Answer 1

In Oracle this can be done by creating a virtual column. When the table is accessed the virtual columns appear to be the same as the normal columns, however their value is derived/calculated instead of being stored in memory.

The syntax for defining a virtual column is as follows:

NOME_DA_COLUNA [TIPO_DE_DADOS] [GENERATED ALWAYS] AS (EXPRESSION) [VIRTUAL]

Here’s an example of how to change an existing table to add a virtual column:

ALTER TABLE tabela_X
  ADD TURNO GENERATED ALWAYS AS (CASE WHEN TO_CHAR(COLUNA_DATE, 'hh24:mi:ss') < '12:00:00' THEN 'Manhã' WHEN TO_CHAR(COLUNA_DATE, 'hh24:mi:ss') < '18:00:00' THEN 'Tarde' ELSE 'Noite' END));

In this example, the hours in the interval 00:00 until 12:00 correspond to the morning shift, between 12:00 and 18:00 correspond to the afternoon and the rest correspond to the night shift.

It will therefore be necessary to change the condition in the CASE instruction according to your need.