How to convert milliseconds to date string in Oracle SQL

If you store the date in milliseconds, for instance, 1126483200000, its milliseconds since January 1, 1970, 00:00:00 GMT in Oracle, we can see, even one second converts to thousand milliseconds, its just a huge number which means nothing and hard to read. So usually we have to write Oracle SQL query statement to get this milliseconds and convert to a date string format as instead:

Solution: Use the following SQL:

select to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') +
(1126483200000)/1000/60/60/24 , 'YYYY-MM-DD HH12:MI:SS am') datestr from dual

It converts milliseconds 1126483200000 to date format ‘YYYY-MM-DD HH12:MI:SS am’ display, when I execute this SQL, the output date was 2005-09-12 12:00:00 am. You can change the milliseconds to the appreciate value and sent date(We used 1970-01-01 00).

If you want to output a different time string format, just change pattern ’YYYY-MM-DD HH12:MI:SS am’, the following example demonstrates how to output 24hr time frame.

select to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24') +
(1126483200000)/1000/60/60/24 , 'HH24:MI:SS')
datestr from dual;