Valor númerico en la columna SNAPSHOT_TIME de la vista cdb_pdb_snapshots

El día de hoy estaba dando una clase sobre la característica de PDB Snapshot Carrusel., introducida en la versión 18c de la base de datos Oracle.

A la hora de ampliar la información sobre los snapshots creados, me topé con la columna SNAPSHOT_TIME, en la vista del diccionario de la base de datos cdb_pdb_snapshots.

Al inicio pensé que era formato Juliano, pero luego, apreciando más el valor, el número era muy grande para serlo.

Cuando ejecuté la consulta a continuación, obtengo un error con el mensaje ORA-01830: date format picture ends before converting entire input string

SQL> select to_date(julian_date,'JSP') from ( select 1749086154 as julian_date from dual);

Investigando un poco me encontré, que en Oracle 19c en la vista DBA_PDB_SNAPSHOTS la columna SNAPSHOT_TIME representa un valor de timestamp en formato número, más específicamente un número que codifica el número de segundos desde el Unix epoch (01/01/1970 00:00:00 UTC).

Por tanto, si quiere verlo en un valor entendible para los mortales, es necesario hacer la consulta de la siguiente manera:

SELECT con_name, snapshot_name, snapshot_scn,
TO_CHAR(TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(SNAPSHOT_TIME, 'SECOND'),'DD/MM/YYYY HH24:MI') as TIME_SNAP,
full_snapshot_path
FROM cdb_pdb_snapshots
ORDER BY con_id, snapshot_scn;

-SNAPSHOT_TIME es el número de segundos desde el Unix epoch (01/01/1970 00:00:00 UTC).

-NUMTODSINTERVAL(SNAPSHOT_TIME, 'SECOND') convierte ese número a un intervalo de tipo INTERVAL DAY TO SECOND.

-Se lo sumás a TO_DATE('1970-01-01','YYYY-MM-DD') para obtener un valor DATE.

-TO_CHAR(..., 'DD/MM/YYYY HH24:MI') convierte la fecha a texto con el formato deseado.

Si el ambiente esta en la nube de OCI, vas a tener el horario del datacenter de la región que escogiste para hacer el despliegue del servicio. Si quieres verlo en tu horario local, - en mi caso para Costa Rica, tu lo puedes ajustar a tu zona horaria-, lo puedes hacer así:

SELECT con_name, snapshot_name, snapshot_scn,
TO_CHAR( FROM_TZ( CAST(TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(SNAPSHOT_TIME, 'SECOND') AS TIMESTAMP),
'UTC' ) AT TIME ZONE 'America/Costa_Rica', 'DD/MM/YYYY HH24:MI' )
AS SNAPSHOT_DATE_LOCAL,
full_snapshot_path
FROM cdb_pdb_snapshots
ORDER BY con_id, snapshot_scn;

Si llegan a toparse con un número parecido al de esta columna, verifiquen en la información oficial de la versión, quizás sea el mismo caso del que les hablé acá.