Oracle — 独立的时间和日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13187456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle — separate time and date
提问by user1792928
I have a question with Oracle (I've installed Oracle 11g Express Edition). I want to insert values for 'date' and 'time', but I cannot separate them.
我有一个关于 Oracle 的问题（我已经安装了 Oracle 11g Express Edition）。我想插入“日期”和“时间”的值，但我无法将它们分开。
create table Match
(
numMatch number(2) constraint PKMatch primary key,
dateM date,
heureM date,
numE_Eq number(2),
numE_Eq2 number(2),
nomTerrain varchar2(30)
);
--"tools"=>"preferences"=>"format de date:DD/MM/YYYY HH24:MI:SS"
--"工具"=>"首选项"=>"格式日期：DD/MM/YYYY HH24:MI:SS"
insert into Match values (1,to_date
('10/12/2010','DD/MM/YYYY'),to_date('15:00:00','HH24:MI:SS'),1,3,'Stade Argentina'
);
result:
结果：
dateM: 10/12/2010 00:00:00
heureM: 01/11/2012 15:00:00
PS: I've tried to_char instead of to_date, but it didn't work at all.
PS：我试过 to_char 而不是 to_date，但它根本不起作用。
Yes, I'm aware of that 'DATE datatype contains both date and time', but it's the prof who insists showing date and time separately in the table, and I've seen your solutions before, but for me, it's a query, not to 'insert values' in the table.
是的，我知道“DATE 数据类型包含日期和时间”，但坚持在表中分别显示日期和时间的是教授，我以前看过你的解决方案，但对我来说，这是一个查询，不要在表中“插入值”。
So I'd like to know how I can have a table directly presenting date and time.
所以我想知道如何让表格直接显示日期和时间。
回答by Josh Smeaton
Oracle doesn't have a TIME datatype. You can store a DATE with a time component, and just query based on time, and display based on time.
Oracle 没有 TIME 数据类型。您可以存储带有时间组件的 DATE，只需根据时间进行查询，并根据时间进行显示。
select to_char(my_date_field, 'HH24:MI:SS')
from my_table
where to_date(my_date_field, 'HH24:MI') = '18:51';
Alternatively, you can store seconds from midnight as an integer, and calculate the time of day from that. It will also make querying for range times easier I think.
或者，您可以将午夜的秒数存储为整数，并从中计算一天中的时间。我认为它还将使查询范围时间更容易。
Also, within a session, execute the following to have all dates formatted the way you wish:
此外，在会话中，执行以下操作以按照您希望的方式格式化所有日期：
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
回答by Seva Safris
Another way to represent a TIME equivalent type in Oracle is with the INTERVAL type, such as:
在 Oracle 中表示 TIME 等效类型的另一种方法是使用 INTERVAL 类型，例如：
SQL> CREATE TABLE foo (
bar INTERVAL DAY(0) TO SECOND(3)
);
This would allow the storage of a time period with 0 precision of the DAY component, and 3 decimal points for the SECOND component. An INSERT example is:
这将允许以 0 精度的 DAY 组件和 3 个小数点为 SECOND 组件存储时间段。插入示例是：
SQL> INSERT INTO foo VALUES ('0 01:01:01.333');
What's great about this approach is that it automatically presents the results of a SELECT in an intuitive format without the need for conversion:
这种方法的优点在于，它以直观的格式自动呈现 SELECT 的结果，无需转换：
SQL> SELECT * FROM foo;
BAR
---------------------------------------------------------------------------
+0 01:01:01.333
回答by a_horse_with_no_name
but it's the prof who insists showing date and time separately in the table
但坚持在表格中分别显示日期和时间的是教授
While this sounds like a pretty stupid requirement, one thing you could do is to create two computed columns that show the date and time as varchar columns:
虽然这听起来很愚蠢，但您可以做的一件事是创建两个计算列，将日期和时间显示为 varchar 列：
create table match
(
nummatch number(2) constraint pkmatch primary key,
the_date date,
datem generated always as to_char(the_date, 'yyyy-mm-dd'),
heurem generated always as to_char(the_date, 'hh24:mi')
nume_eq number(2),
nume_eq2 number(2),
nomterrain varchar2(30)
);
insert into Match (nummatch, the_date, nume_eq, nume_eq2, nomterrain)
values
(1,to_date('10/12/2010 15:00:00','DD/MM/YYYY hh24:mi:ss'),1,3,'Stade Argentina');
Then a
然后一个
select *
from match;
will return:
将返回：
NUMMATCH | THE_DATE | DATEM | HEUREM | NUME_EQ | NUME_EQ2 | NOMTERRAIN
---------+---------------------+------------+--------+---------+----------+----------------
1 | 2010-12-10 15:00:00 | 2010-12-10 | 15:00 | 1 | 3 | Stade Argentina
Alternatively you could just create a view on the table that separates the date and time using to_char()
或者，您可以在表格上创建一个视图，使用分隔日期和时间 to_char()
回答by Jonathan Leffler
Oracle DATE type includes both DATE and TIME information (because it pre-dates the SQL-92 standard when the standard DATE, TIME and TIMESTAMP types were added). So, you can't separate them in the table; there's no reason to do so, either. You can, if you so desire, create a view which presents the DATE field as separate date-only and time-only display fields.
Oracle DATE 类型包括 DATE 和 TIME 信息（因为当添加标准 DATE、TIME 和 TIMESTAMP 类型时，它早于 SQL-92 标准）。所以，你不能在表格中将它们分开；也没有理由这样做。如果您愿意，您可以创建一个视图，将 DATE 字段显示为单独的仅日期和仅时间显示字段。
