We help IT Professionals succeed at work.
Get Started

How to translate epoch date (number) to oracle date

2,193 Views
Last Modified: 2015-03-24
I studied this question for epoch dates but still need a little help.

My date field (as a number) = 1426737600, which represents 3/30/2015 (seconds are not in the mix, in regards to formatting).

I tried this from the linked question:
WITH sampledata
     AS (SELECT 1426737600 n, TO_DATE('2015-03-24', 'yyyy-mm-dd') gmt FROM DUAL)
SELECT n, gmt, DATE '1601-01-01' + ROUND(n / 864000000000, 5) AS new_date
  FROM sampledata;

which returns
N                      GMT              NEW_DATE
1426737600      3/24/2015      1/1/1601 12:02:23 AM

The documentation I have (Remedy ticket system) says this:
Date fields, which store only the date, as the number of days from the beginning of its range. Use a Date field when you want to compare two dates or perform calculations based on the date, such as calculating the number of days between two dates. Date fields can accept values from January 1, 4713 B.C. to January 1, 9999.

Time fields, which store only the time, as the number of seconds from 12:00:00 a.m. Use a Time field when you want to compare two times or perform calculations based on time, such as the number of seconds elapsed. The value in a time field is independent of the time zone. While a Date/Time field will adjust the displayed value to reflect the user’s time zone, the time value will remain unchanged when displayed.

Date/time fields, which store the calendar date and time together. These fields can be configured so that only the date or time is visible. Values before January 1, 1970 cannot be entered in date/time fields.
Comment
Watch Question
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This problem has been solved!
Unlock 3 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE