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;
How to convert the milliseconds to date time for a certain time zone. The code should be generic so that it takes local time zone.
Thank’s, all morning i was looking for this solution!!!
greetins from colombia