I want to SELECT a TIMESTAMP(6) with milliseconds, but at the same time I need to subtract 3 hours (0.125 of a day) from that TIMESTAMP to convert it to my timezone. So I tried:
SELECT To_Char(UTCSCANTIME-0.125,'YYYY-MM-DD HH24:MI:SS') AS LOCALSCANTIME
Outcome: 2018-08-01 19:22:39
If I append "FF" to show milliseconds:
SELECT To_Char(UTCSCANTIME-0.125,'YYYY-MM-DD HH24:MI:SS.FF') AS LOCALSCANTIME
Outcome: ORA-01821: date format not recognized
However if I keep the "FF" but I don't subtract 0.125:
SELECT To_Char(UTCSCANTIME,'YYYY-MM-DD HH24:MI:SS.FF') AS LOCALSCANTIME
Outcome: 2018-08-01 22:22:39.259000
How can I achieve both things?
Thanks in advance!
Subtracting a NUMBER value (being a number of days) from a datetime value is an Oracle-specific operation (not in SQL Standard) and it is supported for the DATE data type only. When you attempt this operation on a TIMESTAMP(6) value, the database performs an implicit conversion from TIMESTAMP(6) to DATE. This is why you cannot use a TIMESTAMP-specific format model element FF on the result: DATE does not store fractional seconds. To achieve what you want, use a day-to-seconds interval:
select to_char(UTCSCANTIME - interval '3' hour,'YYYY-MM-DD HH24:MI:SS.FF') as LOCALSCANTIME from ...