Why are there many zeros on the time?-CodePudding

INPUT:

INSERT INTO APPOINTMENT (A_DATETIME, CLIENT_ID, PET_ID, VN_ID) VALUES (TO_DATE('2023-01-20 09:30', 'YYYY-MM-DD hh24:mi'), 1001, 10001, 801);

OUTPUT:

   APP_ID A_DATETIME                    CLIENT_ID     PET_ID      VN_ID
---------- ---------------------------- ---------- ---------- ----------
         1 20-JAN-23 09.30.00.000000000       1001      10001        801

CodePudding user response：

Your A_DATETIME column is not a DATE data type but a TIMESTAMP(9) data type and has 9 fractional digits for the seconds.

When you insert the DATE then it will be implicitly cast to the same data type as the column and will have fractional seconds.

CREATE TABLE APPOINTMENT (
  A_DATETIME TIMESTAMP(9),
  CLIENT_ID  NUMBER,
  PET_ID     NUMBER,
  VN_ID      NUMBER
);

INSERT INTO APPOINTMENT (A_DATETIME, CLIENT_ID, PET_ID, VN_ID)
  VALUES (TO_DATE('2023-01-20 09:30', 'YYYY-MM-DD hh24:mi'), 1001, 10001, 801);

Then the table contains:

A_DATETIME	CLIENT_ID	PET_ID	VN_ID
2023-01-20 09:30:00.000000000	1001	10001	801

If you want to only store integer seconds then use a DATE or TIMESTAMP(0) data type:

ALTER TABLE appointment MODIFY a_datetime DATE;

Then the table contains:

A_DATETIME	CLIENT_ID	PET_ID	VN_ID
2023-01-20 09:30:00	1001	10001	801

Note: a DATE column ALWAYS contains year, month, day, hour, minute and second components and stores it in a binary format. However, the client application (i.e. SQL*Plus, SQL Developer, Java, PHP, etc.) you are using to view the data will format that binary data to display it and the default format it uses may only show the date components and not the time components; that does not mean the the time components do not exist, only that you need to change how the client application displays dates.

fiddle