Oracle —时间和日期分开 [英] Oracle — separate time and date

问题描述

我对Oracle有疑问(我已经安装了Oracle 11g Express Edition). 我想为日期"和时间"插入值，但是我无法将它们分开.

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.

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

-工具" =>首选项" =>日期格式:DD/MM/YYYY HH24:MI:SS"

--"tools"=>"preferences"=>"format de date: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:我尝试使用to_char而不是to_date，但是它根本不起作用.

PS: I've tried to_char instead of to_date, but it didn't work at all.

是的，我知道"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.

所以我想知道如何有一个表来直接显示日期和时间.

So I'd like to know how I can have a table directly presenting date and time.

推荐答案

Oracle没有TIME数据类型.您可以将DATE与时间成分一起存储，仅根据时间进行查询，并根据时间进行显示.

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.

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'

这篇关于Oracle —时间和日期分开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！