Post subject: Converting UNIX/Epoch time to Date/Time I have a number field coming from a Remedy db that stores a date in Epoch time format. I need to compare it to a date field using the 'DaysBetween(' function.
Active4 years ago
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?
![Mathematics Mathematics](/uploads/1/2/4/8/124893971/648752788.png)
59.8k16 gold badges310 silver badges489 bronze badges
Josh SmeatonJosh Smeaton
2711 gold badge6 silver badges15 bronze badges
4 Answers
the result of the expression
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
But in this case the return value of the view is a VARCHAR2 and not a DATE value.
miracle173miracle173
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.
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')
user21435user21435
I have used another number to calculate this value.
This script works OK for me.
bpedrosobpedroso
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.
This will also allow an index on UNIX_TIMESTAMP to be used, if there is one.
user21435user21435
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?
Would you like to answer one of these unanswered questions instead?