converting value from System.currentTimeMillis() to readable format
705150Jun 4 2009 — edited Jun 4 2009hi
i have in database coumn with values that were stored as result of executing System.currentTimeMillis() in java (=“the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC). i need to convert these values to some human readable format.
i am able to get year, month and day from these values(value_date) in format "dd/mm/yyyy" but i need hours, minutes and seconds as well.
select i have for getting years, months and days:
select (
extract (day FROM (to_date('01/01/1970 00:00:00000','mm/dd/yyyy hh24:mi:sssss') + value_date/1000/86400))
|| '/'||
extract (month from (to_date('01/01/1970 00:00:00000','mm/dd/yyyy hh24:mi:sssss') + value_date/1000/86400))
||'/'||
extract (year FROM (to_date('01/01/1970 00:00:00000','mm/dd/yyyy hh24:mi:sssss') + value_date/1000/86400))
)
FROM some_table;
please advice how to improve this select to get something like "dd/mm/yyyy hh:mm:ss"
thanks