Adding time stamp values to a table-CodePudding

I have lately started learning SQL,and as a part of my assignment,I was trying to solve this question: The table structure which I have created is:

CREATE TABLE train (train_no int,date_of_dep date,time_of_dep timestamp,time_of_arrival timestamp)

I am trying to insert a record for a train which will depart 33 minutes from now, and will arrive 115 minutes from current time.

This is the command I am trying to use:

INSERT INTO train VALUES(5,SYSDATE,TO_TIMESTAMP(SYSDATE 33/1440),TO_TIMESTAMP(SYSDATE 115/1440))

On displaying the table, it is giving me this output:

Can anyone explain why am I not getting the required output?

CodePudding user response：

As there's no TIME datatype in Oracle, and as you need to collect information up to minutes (you certainly don't need fractional seconds; do you?), use DATE datatype as it contains both date and time information.

Also - although your task says that you do need it - you don't need date of departure; it is contained in time of departure.

Something like this:

SQL> CREATE TABLE train
  2  (
  3     train_no          INT,
  4     time_of_dep       DATE,
  5     time_of_arrival   DATE
  6  );

Table created.

Insert a row; note that it is a good habit to specify all columns you're inserting into. You can add minutes the way you tried to, but - why wouldn't you use interval? It is easier to understand what you're doing:

SQL> INSERT INTO train (train_no, time_of_dep, time_of_arrival)
  2          VALUES (5,
  3                  SYSDATE   INTERVAL '33' MINUTE,
  4                  SYSDATE   INTERVAL '115' MINUTE);

1 row created.

OK, so - what's being inserted?

SQL> SELECT SYSDATE, t.*
  2    FROM train t;

SYSDATE    TRAIN_NO TIME_OF_ TIME_OF_
-------- ---------- -------- --------
11.02.22          5 11.02.22 11.02.22

Whoops! Not very useful. So - modify date format:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';

Session altered.

SQL> SELECT SYSDATE, t.*
  2    FROM train t;

SYSDATE            TRAIN_NO TIME_OF_DEP      TIME_OF_ARRIVAL
---------------- ---------- ---------------- ----------------
11.02.2022 08:33          5 11.02.2022 09:05 11.02.2022 10:27

SQL>

That looks better.

Alternatively, you could have used TO_CHAR function with desired format mask:

SQL> select train_no,
  2    to_date(time_of_dep    , 'dd.mm.yyyy hh24:mi') dep,
  3    to_date(time_of_arrival, 'dd.mm.yyyy hh24:mi') arr
  4  from train;

  TRAIN_NO DEP              ARR
---------- ---------------- ----------------
         5 11.02.2022 09:05 11.02.2022 10:27

SQL>

[EDIT] If it must be a timestamp, you'd do the same:

SQL> CREATE TABLE train
  2  (
  3     train_no          INT,
  4     time_of_dep       timestamp,
  5     time_of_arrival   timestamp
  6  );

Table created.

SQL> INSERT INTO train (train_no, time_of_dep, time_of_arrival)
  2          VALUES (5,
  3                  systimestamp   INTERVAL '33' MINUTE,
  4                  systimestamp   INTERVAL '115' MINUTE);

1 row created.

SQL> SELECT SYSDATE, t.*
  2    FROM train t;

SYSDATE            TRAIN_NO TIME_OF_DEP               TIME_OF_ARRIVAL
---------------- ---------- ------------------------- -------------------------
11.02.2022 08:57          5 11.02.22 09:30:35,783378  11.02.22 10:52:35,783378

SQL>