So, I have a series of columns and I need the maximum, non-null value.
Here is a sample of the problem, and it works as I would expect:
select t.*, (select max(to_timestamp(column_value)) as END_TIME from sys.dbms_debug_vc2coll(PROCESSEND,COMPLETEDTIME,processstart) where COLUMN_VALUE is not null) as END_TIME
from (
select 1 as id, TIMESTAMP '2024-06-20 18:07:50' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:48' as PROCESSEND, null as COMPLETEDTIME from dual
union all
select 2 as id, TIMESTAMP '2024-06-20 18:05:37' as PROCESSSTART, TIMESTAMP '2024-06-20 18:05:19' as PROCESSEND, TIMESTAMP '2024-06-20 18:05:21' as COMPLETEDTIME from dual
union all
select 3 as id, TIMESTAMP '2024-06-20 18:07:43' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:39' as PROCESSEND, TIMESTAMP '2024-06-20 18:07:40' as COMPLETEDTIME from dual
union all
select 4 as id, TIMESTAMP '2024-06-20 18:07:47' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:45' as PROCESSEND, TIMESTAMP '2024-06-20 18:07:46' as COMPLETEDTIME from dual
union all
select 5 as id, TIMESTAMP '2024-06-20 18:07:32' as PROCESSSTART, null as PROCESSEND, TIMESTAMP '2024-06-20 18:07:24' as COMPLETEDTIME from dual
union all
select 6 as id, TIMESTAMP '2024-06-20 18:07:32' as PROCESSSTART, TIMESTAMP '2024-06-20 18:07:20' as PROCESSEND, TIMESTAMP '2024-06-20 18:07:24' as COMPLETEDTIME from dual
union all
select 7 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, null as COMPLETEDTIME from dual
union all
select 8 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, TIMESTAMP '2024-06-20 18:04:40' as COMPLETEDTIME from dual
union all
select 9 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, TIMESTAMP '2024-06-20 18:04:40' as COMPLETEDTIME from dual
union all
select 10 as id, TIMESTAMP '2024-06-20 18:04:59' as PROCESSSTART, TIMESTAMP '2024-06-20 18:04:40' as PROCESSEND, TIMESTAMP '2024-06-20 18:04:40' as COMPLETEDTIME from dual
) t;
And, results as as expected:
| ID | PROCESSSTART | PROCESSEND | COMPLETEDTIME | END_TIME |
|---|---|---|---|---|
| 1 | 2024-06-20 18:07:50.000000000 | 2024-06-20 18:07:48.000000000 | null | 2024-06-20 18:07:50.000000000 |
| 2 | 2024-06-20 18:05:37.000000000 | 2024-06-20 18:05:19.000000000 | 2024-06-20 18:05:21.000000000 | 2024-06-20 18:05:37.000000000 |
| 3 | 2024-06-20 18:07:43.000000000 | 2024-06-20 18:07:39.000000000 | 2024-06-20 18:07:40.000000000 | 2024-06-20 18:07:43.000000000 |
| 4 | 2024-06-20 18:07:47.000000000 | 2024-06-20 18:07:45.000000000 | 2024-06-20 18:07:46.000000000 | 2024-06-20 18:07:47.000000000 |
| 5 | 2024-06-20 18:07:32.000000000 | null | 2024-06-20 18:07:24.000000000 | 2024-06-20 18:07:32.000000000 |
| 6 | 2024-06-20 18:07:32.000000000 | 2024-06-20 18:07:20.000000000 | 2024-06-20 18:07:24.000000000 | 2024-06-20 18:07:32.000000000 |
| 7 | 2024-06-20 18:04:59.000000000 | 2024-06-20 18:04:40.000000000 | null | 2024-06-20 18:04:59.000000000 |
| 8 | 2024-06-20 18:04:59.000000000 | 2024-06-20 18:04:40.000000000 | 2024-06-20 18:04:40.000000000 | 2024-06-20 18:04:59.000000000 |
| 9 | 2024-06-20 18:04:59.000000000 | 2024-06-20 18:04:40.000000000 | 2024-06-20 18:04:40.000000000 | 2024-06-20 18:04:59.000000000 |
| 10 | 2024-06-20 18:04:59.000000000 | 2024-06-20 18:04:40.000000000 | 2024-06-20 18:04:40.000000000 | 2024-06-20 18:04:59.000000000 |
However, when I run this exact same solution against the actual data, I get truncated dates:
| ID | PROCESSSTART | PROCESSEND | COMPLETEDTIME | END_TIME |
|---|---|---|---|---|
| 1 | 2024-05-06 13:53:36 | 2024-05-06 13:53:32 | 2024-05-06 13:53:34 | 2024-05-06 00:00:00.000000000 |
| 2 | 2024-05-06 13:54:21 | 2024-05-06 13:54:17 | 2024-05-06 13:54:19 | 2024-05-06 00:00:00.000000000 |
| 3 | 2024-05-06 13:54:21 | 2024-05-06 13:54:17 | 2024-05-06 13:54:19 | 2024-05-06 00:00:00.000000000 |
| 4 | 2024-05-06 13:54:44 | 2024-05-06 13:54:39 | 2024-05-06 13:54:42 | 2024-05-06 00:00:00.000000000 |
| 5 | 2024-05-06 13:56:47 | 2024-05-06 13:56:42 | 2024-05-06 13:56:45 | 2024-05-06 00:00:00.000000000 |
| 6 | 2024-04-16 15:00:02 | 2024-04-16 15:00:23 | null | 2024-04-16 00:00:00.000000000 |
| 7 | 2024-04-16 07:29:04 | 2024-04-16 07:29:06 | null | 2024-04-16 00:00:00.000000000 |
| 8 | 2024-04-16 07:14:04 | 2024-04-16 07:14:26 | null | 2024-04-16 00:00:00.000000000 |
| 9 | 2024-04-16 07:30:04 | 2024-04-16 07:30:06 | null | 2024-04-16 00:00:00.000000000 |
| 10 | 2024-04-16 07:31:05 | 2024-04-16 07:31:07 | null | 2024-04-16 00:00:00.000000000 |
The data types for these 3 columns in the DB are:
PROCESSSTART DATE
PROCESSEND DATE
COMPLETEDTIME DATE
When running
select max(to_timestamp(column_value)) as END_TIME from sys.dbms_debug_vc2coll(PROCESSEND,COMPLETEDTIME,processstart
The string output I get for column_value is of the form:
20-JUN-24 06.07.50.000000000 PM
I have used to_date and to_date(regexp_replace(COLUMN_VALUE,'.0{1,9} (AM|PM)',''),'DD-MON-YY hh.mi.ss AM') and I still get a truncated date.
Anyone know what’s going on?