Strange date formatting result

InsightVM SQL Queries
1

Hello everyone,

So, I need a report that shows per vulnerability/per asset how long that vulnerability has been found. Looking around I came across some queries that come close to what I want but that are so complex that they far exceed my very basic knowledge about SQL to modify them to my needs. So I decided to take a basic approach and came up with this pretty straightforward query
SELECT
fasvi.vulnerability_id,
fasvi.asset_id,
min(date) as first_detected,
max(date) as last_detected,
min(to_char(date,‘DD-MM-YYYY’)) as first_ddmmyyyy,
max(to_char(date,‘DD-MM-YYYY’)) as last_ddmmyyyy
FROM fact_asset_scan_vulnerability_instance fasvi
WHERE vulnerability_id = 88 and asset_id = 79625
GROUP BY fasvi.vulnerability_id, fasvi.asset_id

Now, depending on the value of ‘date’ I get inconsistent results, i.e
first_detected, last_detected, first_ddmmyyyy, last_ddmmyyyy
2022-03-28 04:33:58.341, 2022-04-11 04:33:00.775, 04-04-2022, 28-03-2022
2021-11-15 04:18:40.245, 2021-11-26 11:21:26.344, 15-11-2021, 26-11-2021
So with the first one first_ddmmyyyy and last_ddmmyyyy seem to have been swapped but with the second one everything appears to be fine.
Assuming that I do something wrong can anyone point me in the right direction?

Cheers, Ron

2

I think this is happening due to the order of operations with these lines:

min(to_char(date,‘DD-MM-YYYY’)) as first_ddmmyyyy,
max(to_char(date,‘DD-MM-YYYY’)) as last_ddmmyyyy

The to_char on the inside is converting that timestamp to a string, and then the min/max is being done on the string that’s output, rather than the numeric timestamp, which is going to give some wonky results like you posted.

I swapped it so the min/max is done on the date itself first, and then the to_char is done on the resulting value. I think that should result in matching dates for those last two columns.

SELECT
fasvi.vulnerability_id,
fasvi.asset_id,
min(date) as "first_detected",
max(date) as "last_detected",
to_char(min(date),'DD-MM-YYYY') as first_ddmmyyyy,
to_char(max(date),'DD-MM-YYYY') as last_ddmmyyyy
FROM fact_asset_scan_vulnerability_instance fasvi
WHERE vulnerability_id = 88 and asset_id = 79625
GROUP BY fasvi.vulnerability_id, fasvi.asset_id
3

Hi Holly, just back from a short leave. Thanks for that.

1 Like