Oracle中移除时间戳日期及筛选每日9-10点数据的技术问询

Hey there! Let's break down your Oracle datetime questions clearly and practically:

Depending on what you need the end result to be, there are a few handy approaches:

• If you just want to display the time as a readable string:
  Use TO_CHAR to format the timestamp to show only hours, minutes, seconds, and fractional seconds (tweak the format mask if you don't need fractional seconds):

  SELECT TO_CHAR(your_timestamp_column, 'HH24:MI:SS.FF') AS time_only
  FROM your_table;
  

  For example, 01-JAN-19 09.00.00.000000000 would output as 09:00:00.000000000.

• If you need an interval representing time since midnight:
  Subtract the truncated timestamp (which resets to midnight of the same day) from the original value:

  SELECT your_timestamp_column - TRUNC(your_timestamp_column) AS time_interval
  FROM your_table;
  

  This gives you an INTERVAL DAY TO SECOND result like 0 09:00:00.000000000.

• If you want a DATE type with the original time (date part set to today):
  Combine today's truncated date with the time interval from your timestamp:

  SELECT TRUNC(SYSDATE) + (your_timestamp_column - TRUNC(your_timestamp_column)) AS date_with_time
  FROM your_table;
  

To filter records where closed_date falls between 9 AM and 10 AM (inclusive of 9:00:00, exclusive of 10:00:00 to avoid overlap), here are two reliable methods:

Recommended Method (Index-Friendly) #

This approach avoids applying functions directly to the closed_date column, so it can leverage any existing indexes on the column—critical for large datasets:

SELECT *
FROM your_table
WHERE closed_date >= TRUNC(closed_date) + INTERVAL '9' HOUR
  AND closed_date < TRUNC(closed_date) + INTERVAL '10' HOUR;

TRUNC(closed_date) resets the timestamp to midnight of the same day, then we add 9 and 10 hours to define your target time window.

Alternative Method (String-Based) #

If you prefer working with formatted strings, extract the hour and minute part and filter on that range:

SELECT *
FROM your_table
WHERE TO_CHAR(closed_date, 'HH24MI') BETWEEN '0900' AND '0959';

Note: This method uses TO_CHAR on closed_date, which means indexes on the column won't be used. Stick with the first method if performance matters.

To extract just the time part for matching records #

Combine the filter with any of the time-extraction methods from question 1:

SELECT TO_CHAR(closed_date, 'HH24:MI:SS.FF') AS closed_time
FROM your_table
WHERE closed_date >= TRUNC(closed_date) + INTERVAL '9' HOUR
  AND closed_date < TRUNC(closed_date) + INTERVAL '10' HOUR;

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