La hora o timestamp UNIX hace referencia al número de segundos (o milisegundos) que han pasado desde el día 1 de enero de 1970 a las 00:00 horas UTC/GMT. Este formato es habitual en logs y a veces se encuentra en tablas de bases de datos, ya que muchas aplicaciones que persisten los datos en ellas suelen almacenar fechas en este formato. La hora UNIX también se conoce con el sobrenombre de UNIX EPOCH.
Habitualmente es posible utilizar la función TO_DATE para convertir las fechas almacenadas como VARCHAR, pero los timestamps permiten almacenar la misma información de una forma más compacta (ocupando menos espacio) aunque ilegible a simple vista. Ante un timestamp lo normal es que se lien los más inexpertos, no consigando realizar bien la conversión a la primera. Por ello se van a utilizar un par de ejemplos en función de si en las consultas el timestamp almacenado son segundos o milisegundos, puesto que son dos de los casos más comunes, pero siempre teniendo en cuenta la zona horaria en la que nos encontramos para evitar fechas incorrectas tras la conversión:
-- Conversión para timestamps en segundos SELECT CAST ( ( TO_TIMESTAMP_TZ( '01/01/1970' || TZ_OFFSET( 'Europe/Madrid' ), 'DD/MM/YYYY HH24:MI:SS TZH:TZM' ) + ( TIMESTAMP_S / 86400 ) ) AS DATE ) FROM DUAL -- Conversión para timestamps en milisegundos SELECT CAST ( ( TO_TIMESTAMP_TZ( '01/01/1970' || TZ_OFFSET( 'Europe/Madrid' ), 'DD/MM/YYYY HH24:MI:SS TZH:TZM' ) + ( TIMESTAMP_MS / 86400000 ) ) AS DATE ) FROM DUAL
Como se puede observar es una conversión sencilla que se puede utilizar en cualquier consulta SQL. Eso sí, si se va a utilizar a menudo (se dispone de un sistema de logs en BD que los almacena en vez de fechas y se comprueban con asiduidad por ejemplo) es mejor crease una función sencilla que nos ahorre tiempo facilitando la reutilización.
Si desea ayudarse de una herramienta para convertir fechas a timestamps o viceversa se recomienda http://www.epochconverter.com/ por ser una de las más completas.
