Oracle -convert HEX datetime to readable

Question

My SCADA’s database using Oracle. It includes 2 fields named “day_part”, “time_part” that I guess it stores date and time. It stored with RAW data type, result query is HEX. I tried to convert, encode raw (hex)to decimal (example 07911C (hex)----495900 (decimal), but I can’t convert to readable datetime. I’ve tried many ways on StackOverFlow but not successfully. number "495900" couldn't be decodeded to any usual day. How can I convert HEX datetime to readable? Thanks

Answer 1

A single example source value with no expected result for that value doesn't give much to work with, but - for that value at least- there is a way to convert it to what looks like a sane date value.

You've tried to convert the whole hex value 07911C to decimal, which gives 495900; and that doesn't seem to be useful.

I wondered if it had encoded the day, month and year (as an offset from some epoch) as three separate pairs of bytes, but splitting into 07, 91 and 1C gives 7, 145 and 28; which also don't seem useful. It could conceivably be July 28th in some year, but the order would be strange, and 145 years after which year - results of 1945, 2045, 2115, 2145 (from 1800, 1900, 1972, 2000) don't seem sensible, assuming SCADA would be recent recordings; but it could use a different epoch.

But it could be encoding the year offset and the day number within that year as two sets of three bytes. Splitting into 079 and 11c gives 121 and 284, and those might make sense against an epoch of 1900-01-01:

select date '1900-01-01'
    + to_number(substr(rawtohex(day_part), 1, 3), 'xxx') * interval '1' year
    + (to_number(substr(rawtohex(day_part), 4, 3), 'xxx') - 1) * interval '1' day
    as result
from your_table

RESULT
----------
2021-10-11

db<>fiddle

rawtohex converts your RAW value to a string; substr gets either the first or last three characters of that string; to_number with the xxx format mask converts those three into a number; and then those numbers are treated as either years or days, and both added to the epoch value 1900-01-01.

The day addition subtracts 1 because if you add 284 to January 1st you get the 285th day. It's possible that's what you want, so the db<>fiddle shows both. If this approach is correct then you can look at the last three bytes of all of your day_part values and see if any are 000 or 16E, which would give a clue. (If any are above 16E then I've guessed wrong, of course).

I'm very aware this is based on shaky foundations, but you can at least use it to look at all your data and see if it gives sensible dates for all of your source values. Ideally you would have at least some values where you do already know the expected result, which would either verify or discount it quickly.