oracle convert unix epoch time to date

by

To convert from milliseconds from epoch (assume epoch is Jan 1st 1970):

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000
from dual;

11/30/2011 5:00:00 AM

To convert that date back to milliseconds:

select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 * 1000
from dual;

1322629200000

If its seconds instead of milliseconds, just omit the 1000 part of the equation:

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * 1322629200
from dual;

select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60
from dual;

Hope that helps.

More Related Contents:

  1. Convert epoch to date in sqlplus / Oracle
  2. How can I convert a Unix timestamp to DateTime and vice versa?
  3. Oracle Age calculation from Date of birth and Today
  4. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  5. How to describe performance issue in relational database?
  6. Convert python datetime to epoch with strftime
  7. How do I escape a reserved word in Oracle?
  8. How to put more than 1000 values into an Oracle IN clause [duplicate]
  9. ORA-30926: unable to get a stable set of rows in the source tables
  10. How to best split csv strings in oracle 9i
  11. TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
  12. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  13. Using the “IN” clause with a comma delimited string from the output of a replace() function in Oracle SQL
  14. Oracle JDBC and Oracle CHAR data type
  15. Does Oracle have an equivalent of SQL Server’s table variables?
  16. Reason why oracle is case sensitive?
  17. Javascript Convert Date Time string to Epoch
  18. Converting epoch time to “real” date/time
  19. Faster alternative in Oracle to SELECT COUNT(*) FROM sometable
  20. Oracle date difference to get number of years
  21. SQL Fiddle Output Error
  22. oracle PL/SQL how to calculate range ip for IPv6 cidr
  23. How do I convert datetime to ISO 8601 in PHP
  24. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  25. Oracle: how to add minutes to a timestamp?
  26. How to drop multiple interval partitions based on date?
  27. How to list active / open connections in Oracle?
  28. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  29. How to generate a version 4 (random) UUID on Oracle?
  30. Find out the history of SQL queries

Leave a Comment