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.