We help IT Professionals succeed at work.

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.
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012
Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2019
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.
Gadsden ConsultingIT Specialist

Author

Commented:
works ! I matched it to some other dates.

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

Thanks.
Gadsden ConsultingIT Specialist

Author

Commented:
Also, I'm not familiar with date 'yyyy-mm-dd' - what format is that ?
Gadsden ConsultingIT Specialist

Author

Commented:
slightwv, thanks as well. The good thing about standards - there's so many to choose from !
Most Valuable Expert 2011
Top Expert 2012
Commented:
>>>> 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.
Gadsden ConsultingIT Specialist

Author

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 ?
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Gadsden ConsultingIT Specialist

Author

Commented:
sdstuber, great ! thanks for the bonus info.