CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloper
Jatinder Singh
Jatinder Singh

Reputation: 317

store only time in HH24:MM format in a table? is it possible?

I want to know if I can store a field called flight_time this is the time the flight departs not how long it takes like someone else suggested making me use the wrong data type of interval....

I want to just store a time and not the current time but one in the future I have the date written out in a separate field flight_date.

Questions

Maybe I am approaching this wrong as I think you can store the time with the date in that one field?

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

What about just using VARCHAR2 because I am going to type it out any way once I populate the table?

Upvotes: 4

Views: 10687

Answers (5)

Wivani
Wivani

Reputation: 2046

Personally I'd go for a custom Object Type but that's because I've been brought up with OO. It will give you plenty of benefits if you approach it well.

Upvotes: 0

Alessandro Rossi
Alessandro Rossi

Reputation: 2450

There is INTERVAL DAY TO SECOND that, with a check constraint to accept only values with 0 seconds, could suite 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>

Upvotes: 1

Kevin Burton
Kevin Burton

Reputation: 11936

You should store flight date and time value as a single column of type date. You can then choose how to format that value for your UI.

If you use 11g you could pre-format it using virtual columns e.g:

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 UI code.

Upvotes: 3

eaolson
eaolson

Reputation: 15094

Oracle does not have the concept of a time of day data type. There's DATE, which includes both the date and time of day. There's TIMESTAMP, which includes fractional seconds.

Datetime calculations in Oracle are done using units of one day. So, SYSDATE+1 is this time tomorrow. SYSDATE+0.25 is 6 hours from now. If you ever want to use this time that you're storing to determine a DATE type value, you should store it as a NUMBER. If all you want is to store a string that looks like a time, you can use VARCHAR2, but you would also be able to store nonsensical values like 66:74.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108686

Oracle's notion of time within a day is fraction-of-a-day.

Accordingly, you could store 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 could display it like this:

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

In the second case you could display it like this:

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

Upvotes: 0

Related Questions