Convert a Unix timestamp to a DATETIME in a View I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out.

This link describes how to do the conversion, but requires changing the nls_date_format to include the time portion. The default currently only shows the date portion. I need the date and time.

Unfortunately, that solution only works at a session level. As a developer, I'd rather not have to go running off to our managed service provider to ask them to change the value at a database level - especially since it may impact other applications.

Is there a way I can convert the timestamp, in sql, to a datetime without modifying the system?
the result of the expression 

to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND')


is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it is done in this example. This automatic conversion to the oracle stringtype VARCHAR2 is controlled by nls_date_format and some server settings. If you want your view to return a VARCHAR2 value with the calculated date in the format 'YYYY-MM-DD HH24:MI:SS' and not a DATE value you can do this by using a conversion function: expression

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND'),'YYYY-MM-DD HH24:MI:SS')


But in this case the return value of the view is a VARCHAR2 and not a DATE value.