Sign up ×  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?  share|improve this question  4 Answers 4  up vote 8
down vote accepted  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
d