Only store time in HH24: MM format in a table? Is it possible?

Question

I want to know if I can save a field called flight_time , this is the time when the flight does not leave as long as someone else suggested that I use the wrong interval data type ....

I just want to save the time, not the current time, but in the future I have a date written in a separate field flight_date .

Questions

Perhaps I am approaching this incorrectly, since I think that you can save time with a date in this one field?

Is there any data type where I can mask the format, but I don't want to convert anything?

How about using VARCHAR2 because I'm going to enter it in some way after populating the table?

Answer 1

You must store the date and time of the flight as a single type date column. You can then choose how to format this value for your interface.

If you use 11g, you can pre-format it using virtual columns, for example:

 create table t1 ( flight_date date, flight_time as (to_char(flight_date,'HH24:SS:MI')), flight_day as (to_char(flight_date,'dd/mm/yyyy')) ); 

although formatting should probably be done in your user interface code.

Answer 2

Oracle has no concept of a data type such as time. There is a DATE , which includes both the date and time of day. There is TIMESTAMP , which includes fractional seconds.

Datetime calculations in Oracle are performed using units of one day. So, SYSDATE+1 this time tomorrow. SYSDATE+0.25 - after 6 hours. If you want to use this time that you store to determine a value of type DATE , you must save it as NUMBER . If you want to save a string that looks like time, you can use VARCHAR2 , but you can also store meaningless values ​​like 66:74.

Answer 3

There is an INTERVAL DAY TO SECOND , which with a check restriction only accepts values ​​with 0 seconds, can be set for your needs:

 SQL> create table flight ( 2 time interval day(0) to second (0), 3 constraint chk_time 4 check( 5 extract(second from time) = 0 6 ) 7 ) 8 / Table created SQL> insert into flight(time) 2 values (interval '10:30' hour to minute) 3 / 1 row inserted SQL> insert into flight(time) 2 values (interval '10:30:30' hour to second) 3 / insert into flight(time) values (interval '10:30:30' hour to second) ORA-02290: check constraint (ESTCEDAR.CHK_TIME) violated SQL> insert into flight(time) 2 values (interval '23:30' hour to minute) 3 / 1 row inserted SQL> insert into flight(time) 2 values (interval '24:30' hour to minute) 3 / insert into flight(time) values (interval '24:30' hour to minute) ORA-01873: the leading precision of the interval is too small SQL> insert into flight(time) 2 values (interval '1 10:30:30' day to second) 3 / insert into flight(time) values (interval '1 10:30:30' day to second) ORA-01873: the leading precision of the interval is too small SQL> select * 2 from flight 3 / TIME ------------------- +0 10:30:00 +0 23:30:00 SQL> 

Answer 4

Oracle concept of time during the day is a fractional day.

Accordingly, you can save the time as a floating point number between zero and one, or as an integer number of minutes between 0 and 1439, for example.

In the first case, you can display it as follows:

TO_CHAR (TRUNC (SYSDATE) + FRACTIME, 'HH24: MI: SS')

In the second case, you can display it as follows:

TO_CHAR (TRUNC (SYSDATE) + (MINUTES / 1440), 'HH24: MI: SS')

Answer 5

Personally, I would go for a custom object type, but that is because I was raised with OO. This will give you many benefits if you approach it well.