• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1463
  • Last Modified:

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 Consulting
Gadsden Consulting
  • 5
  • 3
3 Solutions
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
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.
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.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Gadsden ConsultingIT SpecialistAuthor Commented:
Also, I'm not familiar with date 'yyyy-mm-dd' - what format is that ?
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv, thanks as well. The good thing about standards - there's so many to choose from !
>>>> 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

           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 SpecialistAuthor Commented:

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 ?
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 ?


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)

Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber, great ! thanks for the bonus info.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now