SQLplus中建表无需输入日期仅存时间并计算通话时长咨询

1. 查询仅显示日期的根因 #

你当前的存储逻辑本身没有问题，Oracle的DATE类型默认同时存储日期+时间信息，查询仅显示日期是会话默认的NLS_DATE_FORMAT参数仅配置了日期部分的显示规则，并非时间部分没有存入数据库。你可以执行以下命令临时修改当前会话的显示格式验证数据完整性：

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd hh24:mi:ss';

执行后再查询即可看到完整的日期+时间内容。

2. 调整表设计实现仅输入时间的需求 #

两种方案均可以满足仅输入时间、无需填写日期，同时支持正常计算通话时长的要求：

方案一：保留DATE类型，统一固定日期部分 #

无需修改现有表的字段类型，插入时统一把日期部分写死为固定值即可，业务侧仅需要输入时分秒：

• 插入示例：

-- 仅需要替换后面的时分秒内容，日期部分固定为统一值即可
INSERT INTO MOBILE_TRANSACTION (call_start_time, call_end_time) 
VALUES (
    to_date('0001/01/01 ' || '20:20:20', 'yyyy/mm/dd hh24:mi:ss'),
    to_date('0001/01/01 ' || '20:25:30', 'yyyy/mm/dd hh24:mi:ss')
);

• 计算通话时长（单位：秒）示例：

SELECT (call_end_time - call_start_time) * 86400 AS call_duration 
FROM MOBILE_TRANSACTION;

如果不想每次手动拼接固定日期，可以给字段加默认值约束或者触发器自动补全固定日期部分，插入时仅传入时间字符串即可。

方案二：修改字段类型为INTERVAL类型，原生仅存储时间 #

如果完全不需要日期信息，可以把两个时间字段的类型改为INTERVAL DAY(0) TO SECOND(0)，该类型专门用于存储不带日期的时间值：

• 修改表结构语句：

ALTER TABLE MOBILE_TRANSACTION 
MODIFY (call_start_time INTERVAL DAY(0) TO SECOND(0), call_end_time INTERVAL DAY(0) TO SECOND(0));

• 插入示例（无需输入日期）：

INSERT INTO MOBILE_TRANSACTION (call_start_time, call_end_time)
VALUES (INTERVAL '20:20:20' HOUR TO SECOND, INTERVAL '20:25:30' HOUR TO SECOND);

• 计算通话时长（单位：秒）示例：

SELECT EXTRACT(HOUR FROM (call_end_time - call_start_time)) * 3600 +
       EXTRACT(MINUTE FROM (call_end_time - call_start_time)) * 60 +
       EXTRACT(SECOND FROM (call_end_time - call_start_time)) AS call_duration
FROM MOBILE_TRANSACTION;

3. 自动计算call_duration优化建议 #

可以把call_duration设置为虚拟计算列，无需手动插入值，数据库自动根据两个时间字段计算结果：

• 方案一（DATE类型）添加计算列语句：

ALTER TABLE MOBILE_TRANSACTION DROP COLUMN call_duration;
ALTER TABLE MOBILE_TRANSACTION 
ADD call_duration NUMBER GENERATED ALWAYS AS ((call_end_time - call_start_time) * 86400) VIRTUAL;

• 方案二（INTERVAL类型）添加计算列语句：

ALTER TABLE MOBILE_TRANSACTION DROP COLUMN call_duration;
ALTER TABLE MOBILE_TRANSACTION 
ADD call_duration NUMBER GENERATED ALWAYS AS (
    EXTRACT(HOUR FROM (call_end_time - call_start_time)) * 3600 +
    EXTRACT(MINUTE FROM (call_end_time - call_start_time)) * 60 +
    EXTRACT(SECOND FROM (call_end_time - call_start_time))
) VIRTUAL;

内容的提问来源于stack exchange，提问作者Gyanesh