Oracle中如何将日期字符串与24小时制时间字符串合并为Timestamp

Hey there! Let's walk through exactly how to merge your date and time columns into a proper TIMESTAMP type in Oracle. I'll cover both one-time updates and an auto-updating virtual column option, depending on your needs.

Step 1: Clarify Your Column Details #

First, let's lock in the assumptions about your data:

• Your date column (let's call it date_col) uses the format DD-MON-RR (e.g., 24-JUL-17).
• Your time column (time_col) is a 24-hour string with fractional seconds, formatted like hh:mm:ss.xxxx.

Option 1: Add a Static Timestamp Column (One-Time Update) #

If you want a fixed column that you populate once (or manually refresh later), follow these steps:

1. Create the new timestamp column #

Run this ALTER TABLE command to add the empty column to your table:

ALTER TABLE your_table 
ADD combined_timestamp TIMESTAMP;

(Replace your_table with your actual table name.)

2. Populate the column with merged values #

Use an UPDATE statement to combine the date and time. We'll convert the date string to a timestamp, then add the time as a time interval:

UPDATE your_table 
SET combined_timestamp = TO_TIMESTAMP(date_col, 'DD-MON-RR') + TO_DSINTERVAL('0 ' || time_col);

• TO_TIMESTAMP(date_col, 'DD-MON-RR') converts your date string into a timestamp (the time portion defaults to 00:00:00 if not present in the original date value).
• TO_DSINTERVAL('0 ' || time_col) turns your time string into a day-second interval (the 0 prefix is required because the function expects a days hours:minutes:seconds.fraction format).
• Adding these two values gives you the full timestamp with both date and time components.

Don't forget to commit your changes if you're working in a transactional environment:

COMMIT;

Option 2: Add an Auto-Updating Virtual Column #

If you want the timestamp to automatically sync whenever the date or time column changes, use a virtual generated column:

ALTER TABLE your_table 
ADD combined_timestamp TIMESTAMP 
GENERATED ALWAYS AS (TO_TIMESTAMP(date_col, 'DD-MON-RR') + TO_DSINTERVAL('0 ' || time_col)) 
VIRTUAL;

This column doesn't store data physically—it calculates the value on the fly whenever you query it, so it's always up-to-date without manual updates.

Pre-Check for Invalid Data #

Before running any updates, it's wise to verify your time column has valid formatting. Run this query to spot rows with malformed time strings:

SELECT * 
FROM your_table 
WHERE NOT REGEXP_LIKE(time_col, '^[0-2][0-9]:[0-5][0-9]:[0-5][0-9]\.[0-9]+$');

Fix any invalid rows first to avoid errors during the merge process.

Example Result #

If you have a row where:

• date_col = '24-JUL-17'
• time_col = '14:30:45.6789'

The resulting combined_timestamp will be: 2017-07-24 14:30:45.678900000

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