Oracle中CHAR类型时间求和技术咨询（含双表场景）

Got it, let's break down how to solve this. Since your dept table stores work start/end times as CHAR strings, we can't do direct arithmetic on them—first we need to convert them to proper date/timestamp types, calculate the duration for each record, then sum those durations up.

Step 1: Convert CHAR to Date/Timestamp & Calculate Single Record Duration #

First, let's handle converting the CHAR time strings to a format we can work with. Oracle's TO_DATE or TO_TIMESTAMP functions will do the trick, assuming your time strings follow the HH24:MI:SS format (like your example 10:00:00).

For a single record, the duration in hours would be:

SELECT 
    emp_id,
    (TO_DATE(emp_time_off, 'HH24:MI:SS') - TO_DATE(emp_time_in, 'HH24:MI:SS')) * 24 AS hours_worked
FROM dept
WHERE emp_id = '123456';

• When you subtract two DATE values in Oracle, you get the difference in days. Multiplying by 24 converts that to hours.
• If you need more precision (minutes/seconds), use TO_TIMESTAMP instead to get an INTERVAL type, then extract the components:

SELECT 
    emp_id,
    EXTRACT(HOUR FROM (TO_TIMESTAMP(emp_time_off, 'HH24:MI:SS') - TO_TIMESTAMP(emp_time_in, 'HH24:MI:SS'))) AS hours,
    EXTRACT(MINUTE FROM (TO_TIMESTAMP(emp_time_off, 'HH24:MI:SS') - TO_TIMESTAMP(emp_time_in, 'HH24:MI:SS'))) AS minutes,
    EXTRACT(SECOND FROM (TO_TIMESTAMP(emp_time_off, 'HH24:MI:SS') - TO_TIMESTAMP(emp_time_in, 'HH24:MI:SS'))) AS seconds
FROM dept
WHERE emp_id = '123456';

Step 2: Sum Durations (Per Employee or Total) #

To calculate total hours worked per employee (joining with the employee table for names), use GROUP BY:

SELECT 
    d.emp_id,
    e.emp_name,
    -- Total hours (rounded to 2 decimal places for minutes/seconds)
    ROUND(SUM((TO_DATE(d.emp_time_off, 'HH24:MI:SS') - TO_DATE(d.emp_time_in, 'HH24:MI:SS')) * 24), 2) AS total_hours,
    -- Total minutes (integer)
    SUM((TO_DATE(d.emp_time_off, 'HH24:MI:SS') - TO_DATE(d.emp_time_in, 'HH24:MI:SS')) * 24 * 60) AS total_minutes
FROM dept d
JOIN employee e ON d.emp_id = e.emp_id
GROUP BY d.emp_id, e.emp_name;

If you want the total duration across all employees, just remove the GROUP BY clause:

SELECT 
    ROUND(SUM((TO_DATE(emp_time_off, 'HH24:MI:SS') - TO_DATE(emp_time_in, 'HH24:MI:SS')) * 24), 2) AS company_total_hours
FROM dept;

Important Notes #

• Validate Time Formats: If some CHAR time strings don't match HH24:MI:SS, your query will throw errors. Use VALIDATE_CONVERSION to find bad records first:

  SELECT emp_id, emp_time_in, emp_time_off
  FROM dept
  WHERE VALIDATE_CONVERSION(emp_time_in AS DATE, 'HH24:MI:SS') = 0
     OR VALIDATE_CONVERSION(emp_time_off AS DATE, 'HH24:MI:SS') = 0;
  

  This returns any records where the time string can't be converted to a valid date with your format mask.
• Adjust Format Masks: If your time strings use 12-hour format (e.g., 10:00:00 AM), change the format mask to HH:MI:SS AM in TO_DATE/TO_TIMESTAMP.

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