7

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
12

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
5

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
  • Doesn't look OK to me. – Colin 't Hart Jul 28 '14 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 '15 at 11:27
  • This is the case if the timestamp is being stored by Hibernate. – Daniel Gray Aug 25 '16 at 16:13
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

protected by Paul White♦ Apr 22 '15 at 10:12

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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