How to convert milliseconds to the proper date format

Here is how to convert milliseconds to the proper date format:

  
select 
TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS') + (1016026599210 /1000/60/60/24) from dual

This is very simple and useful. For example, if you want to get some records from your order table for a time period, just use this script in the WHERE clause and make your life easier. (Note: Remember that the date format I am using here dd/mm/yyyy (UK standard format) instead of mm/dd/yyy (US format)).

For example, let's say that we have a table called vendororder with the following columns:

orderid varchar2(10), 
creation_ts long, 
vendorid long,
customerid varchar2(10)

The column creation_ts is the timestamp when the order had been placed. Instead of choosing datatype 'DATE', sometimes you may need to choose datatype 'long', which is nothing but the millisecond of the date. In this case, it is very difficult to write a SQL statement to, for example, return the orders created in the month of May, 2002. What we have to do is: 1) convert the date '01-May-2002 00:00:00' and '31-May-2002 23:59:59' to milliseconds first (eg, millisec01May and millisec31May) and then 2) write the SQL like this:

  SELECT * 
  FROM vendororder
  WHERE creation_ts between millisec01May AND millisec31May.

Instead of doing these two steps seperately, we can use the following SQL:

  SELECT * 
  FROM vendororder
  WHERE to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+(creation_ts/1000/60/60/24) 
     between to_date('01/05/2002 00:00:00','dd/mm/yyyy hh24:mi:ss') 
     and to_date('31/05/2002 23:59:59','dd/mm/yyyy hh24:mi:ss')

I think this is very useful if run from the SQL prompt.

For More Information

• What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.