Search code examples
sqloracleoracle11goracle10g

Adding time stamp values to a table

I have lately started learning SQL,and as a part of my assignment,I was trying to solve this question: enter image description here 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: enter image description here

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

Solution

  • 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_char(time_of_dep    , 'dd.mm.yyyy hh24:mi') dep,
  3    to_char(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>