8

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?

Improve this question
0

4 Answers 4

Reset to default
14

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.

Improve this answer
0
6

Best practice is to avoid Non-SQL function calls inside SQL queries as the function may not be universally available -- in my system there is no numtodsinterval function -- also because of the additional overhead to switch context from SQL to PL/SQL and then back to SQL for every row returned or tested if the function is called in the where clause.

Here is a SQL-Only solution.

TO_DATE('1970-01-01','YYYY-MM-DD') + unix_timestamp / 86400

Note that the result is an Oracle DATE value which contains also the TIME. If it does not appear to have TIME when you run the query, then the problem is in your NLS_DATE_FORMAT or your UI's implementation of converting dates to strings. Be sure to do TO_CHAR yourself with the time portion in the format string in order to see the whole value. e.g. TO_CHAR(a_date, 'yyyy-mm-dd hh24:mi:ss')

Improve this answer
1

I have used another number to calculate this value.

select (TO_DATE('1970-01-01','YYYY-MM-DD') + unix_timestamp / 86400000) from dbname;

This script works OK for me.

Improve this answer
4
  • Doesn't look OK to me.
    – Colin 't Hart
    Jul 28, 2014 at 19:13
  • It's ok if and only if unix_timestamp is given in milliseconds (which would be quite unusual, though).
    – Frank Schmitt
    May 20, 2015 at 11:27
  • This is the case if the timestamp is being stored by Hibernate.
    – Daniel Gray
    Aug 25, 2016 at 16:13
  • Very common with java software in general to store a unix-timestamp-like value in milliseconds.
    – Dan Pritts
    Oct 15, 2019 at 20:42
0

If the unix_timestamp must be compared to some date range in the WHERE-clause, it is best to convert the dates to unix_timestamps instead of converting all of the unix_timestamp values to dates.

For example, the following can find the rows with UNIX_TIMESTAMP between the specified start date and end date.

WHERE unix_timestamp BETWEEN
       (:start_date - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400
       AND 
       (:end_date - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400

This will also allow an index on UNIX_TIMESTAMP to be used, if there is one.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.