oracle oracle仅插入时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12215281/
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 insert only time
提问by user1164545
I would like to insert data to db as time, not date. If I use to_date('2012-08-31 07:39:33', 'YYYY-MM-DD HH24:MI:SS')it adds date too.
If I use to_date('09:34:00', 'HH24:MI:SS')it adds year, month, day as well, from nowhere :|
Later I need to get rows where time is between x and y, not taking in account the year, month or day. How do I do that?
thanks
我想将数据作为时间而不是日期插入到数据库中。如果我使用to_date('2012-08-31 07:39:33', 'YYYY-MM-DD HH24:MI:SS')它也会添加日期。如果我使用to_date('09:34:00', 'HH24:MI:SS')它，它还会从无处添加年、月、日：| 后来我需要获取时间在 x 和 y 之间的行，而不考虑年、月或日。我怎么做？谢谢
采纳答案by Dave Costa
A DATEtype always includes the date component.
甲DATE类型总是包括日期分量。
One option is to continue using DATEand write your code to ignore the date component. In order to make queries on the time efficient, you might want to create a function-based index on something like TO_CHAR( date_field, 'HH24:MI:SS' )and use that expression in your queries.
一种选择是继续使用DATE并编写代码以忽略日期组件。为了使查询更高效，您可能希望在类似的东西上创建基于函数的索引，TO_CHAR( date_field, 'HH24:MI:SS' )并在查询中使用该表达式。
Alternatively, you could use a NUMBERfield to store the number of seconds since midnight, and write your queries in terms of that.
或者，您可以使用一个NUMBER字段来存储自午夜以来的秒数，并据此编写查询。
回答by Alex Poole
As an alternative to the datesolution Dave shows, you could use an intervaldata type for the column:
作为dateDave 展示的解决方案的替代方案，您可以interval为列使用一种数据类型：
create table t42(id number, t interval day to second);
insert into t42 (id, t) values(123, to_dsinterval('0 07:39:33'));
insert into t42 (id, t) values(456, to_dsinterval('0 09:34:00'));
select id
from t42
where t between to_dsinterval('0 07:00:00') and to_dsinterval('0 07:59:59');
ID
----------
123
Displaying intervals is a little awkward as they don't have format models, but see this questionfor some ideas if needed. If you only use them for filtering then that may not be an issue at all.
显示间隔有点尴尬，因为它们没有格式模型，但如果需要，请参阅此问题以获取一些想法。如果您仅将它们用于过滤，那么这可能根本不是问题。
回答by user1043173
you can use number column type and insert value as
您可以使用数字列类型并插入值作为
INSERT INTO table_name (nTime)
VALUES (date - trunc(date));
and then select values
然后选择值
select *
from table_name t
where t.nTime between (10 / 24 + 15 / 24 / 60) and (12 / 24 + 30 / 24 / 60) --between 10:15 and 12:30
