在表中只存储HH24：MM格式的时间？可能吗？

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

我想知道我是否可以存储一个名为flight_time的字段,这是航班离开的时间,而不是像其他人建议让我使用错误的数据类型的间隔所花费的时间....

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.

我想只存储一个时间而不是当前时间,但是将来我会在一个单独的字段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?

那么只使用VARCHAR2,因为一旦填充表格,我会以任何方式输入它?

5 个解决方案

#1

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.

您应将航班日期和时间值存储为日期类型的单个列。然后,您可以选择如何为UI设置该值的格式。

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

如果您使用11g,您可以使用虚拟列预先格式化它,例如:

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.

虽然格式化可能应该在您的UI代码中完成。

#2

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.

Oracle没有时间数据类型的概念。有DATE,包括日期和时间。有TIMESTAMP,包括小数秒。

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.

Oracle中的日期时间计算使用一天的单位完成。所以,SYSDATE + 1明天就是这个时候。 SYSDATE + 0.25从现在起6小时。如果您希望使用此时间来存储以确定DATE类型值,则应将其存储为NUMBER。如果你想要的只是存储一个看起来像时间的字符串,你可以使用VARCHAR2,但你也可以存储像66:74这样的荒谬值。

#3

There is INTERVAL DAY TO SECOND that, with a check constraint to accept only values with 0 seconds, could suite for your needs:

有一个INTERVAL DAY TO SECOND,通过检查约束只接受0秒的值,可满足您的需求:

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>

#4

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.

因此,您可以将时间存储为0到1之间的浮点数,或者作为0到1439之间的整数分钟数。

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')

#5

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.

就个人而言,我会选择自定义对象类型,但那是因为我已经被OO所吸引。如果你接近它会给你很多好处。

#1