How to translate epoch date (number) to oracle date

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.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Are you sure about this?

>>>> 1426737600, which represents 3/30/2015

because changing your epoch to the unix epoch of 1970-01-01 (as in your date/time field note), you can treat your value as seconds since that epoch

select date '1970-01-01' + 1426737600/86400 from dual;

returns 2015-03-19 04:00:00
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
To add:
If you read the question in the link:  The '1601-01-01' was for LDAP 'epoch' not Unix.

It all comes down to where the number you were given 'starts' its count.  There are a few 'standards' out there.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
works ! I matched it to some other dates.

It is off by GMT offset of 4 hours, I'm in EDT now.

Thanks.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gadsden ConsultingIT SpecialistAuthor Commented:
Also, I'm not familiar with date 'yyyy-mm-dd' - what format is that ?
0
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv, thanks as well. The good thing about standards - there's so many to choose from !
0
sdstuberCommented:
>>>> It is off by GMT offset of 4 hours, I'm in EDT now.

two options

first simple, efficient, but not robust for generic date/time conversions but should probably work for US/Eastern time zone

use TRUNC function

trunc( date '1970-01-01' + 1426737600/86400)


Second, much more robust in that you can use it to translate to any time zone in the world, but must more complicated, use timstamp conversions instead of date


SELECT CAST(
           FROM_TZ(TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL(1426737600, 'second'), 'GMT')
               AT TIME ZONE 'US/Eastern' AS DATE
       )  FROM DUAL;

 >>>  date 'yyyy-mm-dd'

That is an ISO date literal.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber,

TRUNC works for my needs, thanks.

but excellent on  the more generic syntax as well.

>>That is an ISO date literal.
- so, hmmmm, I'm guessing this won't be in my book, "Oracle Database 11g SQL - Master Sql and PL/SQL in the Oracle Database", by Jason Price (Oracle Press) ?

- plus, it's more like Sql Server, right ?
0
sdstuberCommented:
No book is going to have everything, but I wouldn't be surprised if it's in there - but then again, they are described in the Oracle SQL reference so it would be kind of redundant in another book.


>>>> - plus, it's more like Sql Server, right ?

no

it's an ANSI standard (I said ISO earlier, but ANSI is correct) it has nothing to do with sql server (although sql server might support it too, I don't know off hand)

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF51062
0
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber, great ! thanks for the bonus info.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.