sql - PL / SQL每天两次之间更新列(PL/SQL to update column based between two times Daily)

Question

I would like to a SQL code to update the column area based on a interval of time daily.

(我想用一个SQL代码根据每天的时间间隔更新列area 。)

also my start_time column is varchar2 , not date column

(我的start_time列也是varchar2 ，而不是date列)

eg:

(例如：)

between 06:00:00 and 10:00:00 = 'A'<br>
between 10:00:00 and 15:30:00 = 'B'<br>
between 15:30:00 and 22:00:00 = 'C'<br>

table name: myTable

(表名称： myTable)

   id    name        start_time        area
    ============================================
    1     a        06/07/19 11:00        -
    2     b        06/07/19 09:00        -
    3     c        06/07/19 11:00        -
    4     d        07/07/19 13:00        -
    5     e        07/07/19 21:00        - 
    6     f        08/07/19 16:00        -
    7     g        08/07/19 01:00        -
    8     h        08/07/19 18:00        -

Result:

(结果：)

   id    name       start_Time         area
  ============================================
    1     a        06/07/19 11:00        B
    2     b        06/07/19 09:00        A
    3     c        06/07/19 11:00        B
    4     d        07/07/19 13:00        B
    5     e        07/07/19 21:00        C 
    6     f        08/07/19 16:00        C
    7     g        08/07/19 01:00        -
    8     h        08/07/19 18:00        C

I did a SQL query that works:

(我做了一个有效的SQL查询：)

select * 
from myTable 
where  TO_CHAR(TO_DATE(TIME_START,'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') 
       BETWEEN TO_CHAR(TO_DATE('2019/11/11/ 06:00:00','YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') 
       AND TO_CHAR(TO_DATE('2019/11/11/ 13:30:00','YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS')

But I have created a column and now I would like to update it on the records rather than a query.

(但是我创建了一个列，现在我想在记录而不是查询上更新它。)

  ask by paolo ricardos translate from so

Answer 1

You can use to_char() and a case expression:

(您可以使用to_char()和一个case表达式：)

update mytable
    set area = (case when to_char(start_time, 'HH24:MI') >= '06:00' and to_char(start_time, 'HH24:MI') < '10:00'
                     then 'A'
                     when to_char(start_time, 'HH24:MI') >= '10:00' and to_char(start_time, 'HH24:MI') < '15:30'
                     then 'B'
                     when to_char(start_time, 'HH24:MI') >= '15:30' and to_char(start_time, 'HH24:MI') < '22:00'
                     then 'C'
                end);