Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. 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
    
i cannot understand why you think the nls_date_format must be changed and why you think there must be changed something on database level. The nls_date_format is only hanged that the results in this example are displayed in a nice way –  miracle173 Apr 13 '12 at 5:31
    
The default currently only shows the date portion. I need the date and time. Perhaps I should have mentioned that in my question. –  Josh Smeaton Apr 13 '12 at 6:28

4 Answers 4

up vote 4 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 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.

share|improve this answer
    
Understood. This works perfectly. I was confusing myself between django and oracle, where django disambiguates between date and datetime. Thank you! –  Josh Smeaton Apr 13 '12 at 7:08

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')

share|improve this answer

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.

share|improve this answer

I have used other number to calculate this value.

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

This script is OK to me.

share|improve this answer
    
Doesn't look OK to me. –  Colin 't Hart Jul 28 at 19:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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