CODEWITHSUNDEEP
sqloracle-database
qmoazad384c
qmoazad384c

Reputation: 13

How can I set hours and minutes of date with oracle sql?

How can I set hours and minutes of date with oracle sql?

Below is my code.

while venter < sysdate
                loop
                    venter := venter + 1;
                    venter := venter - v1;
                    v1 := dbms_random.value(0, 30) / (24 * 60);
                    venter := venter + v1;

                    vexit := vexit + 1;
                    vexit := vexit - v2;
                    v2 := dbms_random.value(0, 20) / (24 * 60);
                    vexit := vexit + v2;
                    if mod(to_char(venter, 'd'), 7) + 1 in (1, 2) then --weekend
                        continue;
                    end if;
                    insert into testattend values ​​(vseq, venter, vexit, 'normal', null, vclassSeq);

                    vseq := vseq + 1;
                    -- dbms_output.PUT_LINE(vseq);
-- dbms_output.PUT_LINE(venter);
                end loop;

Minutes are added as random values ​​to the values ​​stored in the venter. If this loop continues, the values ​​will accumulate and become abnormal data.

So, I added minus and plus using v1. However, the data is output abnormally.

I wonder if there is a way to leave the venter's day as it is and set only the hour, minute, and second values ​​of 8:45:00 when running the loop.

best regard

Upvotes: 0

Views: 427

Answers (1)

MT0
MT0

Reputation: 167811

I wonder if there is a way to leave the venter's day as it is and set only the hour, minute, and second values ​​of 8:45:00 when running the loop.

You don't need a PL/SQL loop and multiple INSERT statements; you can do it all in a single SQL statement:

INSERT INTO testatend
WITH calendar ( vseq, venter, vexit ) AS (
  SELECT 1,
         DATE '2021-05-01' + INTERVAL '08:45:00' HOUR TO SECOND,
         DATE '2021-05-01' + INTERVAL '17:15:00' HOUR TO SECOND
  FROM   DUAL
UNION ALL
  SELECT vseq + 1,
         venter + CASE TRUNC(venter) - TRUNC(venter, 'IW')
                  WHEN 4 THEN INTERVAL '3' DAY -- Previous day is Friday
                  WHEN 5 THEN INTERVAL '2' DAY -- Previous day is Saturday
                         ELSE INTERVAL '1' DAY -- Prev. day is Sunday to Thursday
                  END,
         vexit  + CASE TRUNC(venter) - TRUNC(venter, 'IW')
                  WHEN 4 THEN INTERVAL '3' DAY -- Previous day is Friday
                  WHEN 5 THEN INTERVAL '2' DAY -- Previous day is Saturday
                         ELSE INTERVAL '1' DAY -- Prev. day is Sunday to Thursday
                  END
  FROM   calendar
  WHERE  venter + INTERVAL '1' DAY < SYSDATE
)
SELECT vseq,
       venter - NUMTODSINTERVAL( DBMS_RANDOM.VALUE(0, 30), 'MINUTE'),
       vexit  - NUMTODSINTERVAL( DBMS_RANDOM.VALUE(0, 30), 'MINUTE'),
       'normal',
       null,
       123 -- vClassSeq
FROM   calendar

(Note: If you want fixed times then do not subtract NUMTODSINTERVAL( DBMS_RANDOM.VALUE(0, 30), 'MINUTE') in the final step.)

Which, for an example table:

CREATE TABLE testatend (
  id        NUMBER,
  time_in   DATE,
  time_out  DATE,
  type      VARCHAR2(20),
  something NUMBER,
  class_id  NUMBER
);

Then inserts 23 rows which may have the values:

ID TIME_IN TIME_OUT TYPE SOMETHING CLASS_ID
1 2021-05-01 08:31:46 2021-05-01 17:01:16 normal 123
2 2021-05-03 08:37:28 2021-05-03 16:48:27 normal 123
3 2021-05-04 08:21:48 2021-05-04 16:49:06 normal 123
4 2021-05-05 08:16:39 2021-05-05 17:12:19 normal 123
5 2021-05-06 08:41:02 2021-05-06 16:49:22 normal 123
6 2021-05-07 08:42:28 2021-05-07 17:09:07 normal 123
7 2021-05-10 08:35:11 2021-05-10 16:46:37 normal 123
8 2021-05-11 08:31:00 2021-05-11 16:56:35 normal 123
9 2021-05-12 08:43:20 2021-05-12 17:05:23 normal 123
10 2021-05-13 08:21:07 2021-05-13 16:50:53 normal 123
11 2021-05-14 08:34:12 2021-05-14 17:01:06 normal 123
12 2021-05-17 08:31:46 2021-05-17 16:45:04 normal 123
13 2021-05-18 08:39:08 2021-05-18 17:04:08 normal 123
14 2021-05-19 08:24:34 2021-05-19 16:53:57 normal 123
15 2021-05-20 08:39:33 2021-05-20 17:12:05 normal 123
16 2021-05-21 08:24:42 2021-05-21 16:46:23 normal 123
17 2021-05-24 08:40:35 2021-05-24 16:52:41 normal 123
18 2021-05-25 08:41:08 2021-05-25 16:47:41 normal 123
19 2021-05-26 08:27:00 2021-05-26 17:04:04 normal 123
20 2021-05-27 08:40:47 2021-05-27 17:00:28 normal 123
21 2021-05-28 08:40:38 2021-05-28 16:52:54 normal 123
22 2021-05-31 08:22:53 2021-05-31 16:45:51 normal 123
23 2021-06-01 08:26:23 2021-06-01 17:14:09 normal 123

db<>fiddle here

Upvotes: 1

Related Questions