insert a time value in oracle-CodePudding

I create a table like this

CREATE TABLE flights_info 
( flight_id number(10) NOT NULL,
  Train_no number(10) NOT NULL,
  station varchar2(50) NOT NULL,
  depature_time date NOT NULL,
  arrival_time date NOT NULL,
  seats_number number(10) NOT NULL, 
  seats_reserved number(10) NOT NULL, 
 flight_date date NOT NULL,
  CONSTRAINT flight_id PRIMARY KEY (flight_id )
);

the fileds depature_time and arrival_time should represent time values so I tried to insert using this script

INSERT INTO flights_info (flight_id,Train_no,station,depature_time,arrival_time,seats_number,seats_reserved,flight_date) VALUES
(1,1,'Station01', to_date('8:00','hh:mi'),to_date('10:00','hh:mi'),30,10, date '2022-05-14');

but I don't why I got date data instead of time.

Output outputs

I'm not sure what is wrong

CodePudding user response：

A DATE data type is a binary data type that consists of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has all of those components even when you only set the some of those components then the others will have the default values set.

Current year and month.
First day of the month.
Zero hours, minutes and seconds.

So if you use:

SELECT to_date('8:00','hh:mi') FROM DUAL;

Then you will get the date that is the 1st day of the current month and year with 8 hours AM and zero minutes and seconds.

I don't why I got date data instead of time.

Because the client application you are using to display the DATE value is set to display only the date component and not the time component of the DATE.

For SQL*Plus and SQL Developer, the default format is set by the NLS_DATE_FORMAT session parameter which you can change using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

For other client applications you will need to look at their preferences (or documentation) to find how the default date format is set.

If you want to only display the time component then use:

SELECT TO_CHAR(arrival_time, 'HH24:MI:SS') AS arrival_time
FROM   flight_info;

Note: the HH or HH12 format models are for a 12-hour clock. You probably want HH24 for a 24-hour clock.

If you want to store a time without a date then use an INTERVAL DAY(0) TO SECOND(0) data type.

CREATE TABLE flights_info(
  flight_id      number(10) NOT NULL,
  Train_no       number(10) NOT NULL,
  station        varchar2(50) NOT NULL,
  depature_time  INTERVAL DAY(0) TO SECOND(0) NOT NULL,
  arrival_time   INTERVAL DAY(0) TO SECOND(0) NOT NULL,
  seats_number   number(10) NOT NULL, 
  seats_reserved number(10) NOT NULL, 
  flight_date    date NOT NULL,
  CONSTRAINT flight_id PRIMARY KEY (flight_id )
);

Then:

INSERT INTO flights_info (
  flight_id,
  Train_no,
  station,
  depature_time,
  arrival_time,
  seats_number,
  seats_reserved,
  flight_date
) VALUES (
  1,
  1,
  'Station01',
  INTERVAL '08:00:00' HOUR TO SECOND,
  INTERVAL '10:00:00' HOUR TO SECOND,
  30,
  10,
  date '2022-05-14'
);

However, while that might look like a good idea, you probably do want use dates and times so that when a flight spans two-or-more days then you can record the arrival and departure date and time (and you won't need the flight_date column as it will be stored in the arrival_time and departure_time columns).