0

I have an issue importing date from a Tririga database into a SQL database. Mainly I cant convert the date properly and it looks like is not the commont format I have seen around.

Eg date value incomming 775724400000

Running something like select to_date('765187200000', 'DDMMYYYYHH24MISS') my_date FROM dual; give me an error ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must be between 1 and last day of month"

I found the following info from this link seems to be also from tririga link_help

And the size of the number are about 10 digits meanwhile this one is 12 and I know for fact this dates should be from the past 10 years (most of them) I can't seem to find anything that gives me an answer how to convert this into proper dates.

Any suggestions?

Alberto
  • 67
  • 1
  • 4
  • It is an Epoch date. Does this answer your question: https://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date – pmdba Aug 05 '21 at 18:04
  • Does this answer your question? [oracle convert unix epoch time to date](https://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date) – astentx Aug 05 '21 at 21:34

1 Answers1

0

The input number appears to be "epoch", a count of milliseconds elapsed since 1 January 1970 GMT (UTC).

To convert to date is not difficult:

select date '1970-01-01' + (775724400000 / 86400000) as dt from dual;

DT                  
--------------------
1994-Aug-01 07:00:00

Note the hard-coded literals: date '1970-01-01' (epoch is by definition measured from midnight on this date) and 86400000. By one of the definitions (in the previous version of the International System of Units and Weights), a second is 1/86400 of a median day. In Oracle, date arithmetic is based on the number 1 representing one day, so to convert your milliseconds to days you must divide your input by 86400 * 1000.

The most delicate question has to do with time zones (and possibly daylight saving time, also related to time zone). In most cases, epoch is measured from midnight on 1 January 1970 GMT, not from midnight on 1 January 1970 in local time. Do you need to adjust for that? Only you and your business users can answer that question.

(As an aside, the number you provided does NOT represent a date in the past 10 years - not even close.)