-4

I need to convert sysdate to a 13 digit number (Unix time stamp number) in oracle. please share the sql for this.

example: 1486015200000 Date: 02/02/2017

for number reference, use 1486015200000 in below link http://www.timestampconvert.com/

dreambigcoder
  • 1,859
  • 4
  • 22
  • 32

3 Answers3

1

The way unix generates timestamps, it's the number of seconds elapsed since midnight Coordinated Universal Time (UTC) of Jan. 1, 1970.

You should be able to use

select (
  timestamp '1970-01-01 00:00:00 GMT' + numtodsinterval(1486015200000 /1000, 'SECOND'))
   at LOCAL 
from dual;

NUMTODSINTERVAL doc

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
Hana
  • 95
  • 3
  • 6
  • The only correct answer which consider time zone probably. However I think the question is for the other way around. – Wernfried Domscheit Mar 16 '17 at 20:44
  • @WernfriedDomscheit - I don't understand the comment about "time zone". My answer definitely contains a comment about that - and also points out that in Oracle the DATE data type DOES NOT CONTAIN information about "time zone", so I am not sure what you meant by that. –  Mar 16 '17 at 21:06
0

In principle this is easy: Take any date. Subtract the date literal date '1970-01-01'. Enclose the difference in parentheses and multiply by 86400000.

That is: the date difference (in Oracle) is in days. A day has 24 * 24 * 60 = 86,400 seconds, and 86,400,000 milli-seconds. The 13-digit "unix time" is the number of milliseconds elapsed since midnight January 1, 1970 UTC.

The computation on the website you linked to is off by 6 hours, so I assume you live somewhere in the Central time zone in the U.S. (or perhaps somewhere else in the same time zone, in North or Central or South America). The web site assumes the input is IN YOUR TIME ZONE. In Oracle, dates are abstract, they are not "in a time zone."

select sysdate, (sysdate - date '1970-01-01') * 86400000 as unix_time from dual;

SYSDATE              UNIX_TIME
-------------------  -------------
03/16/2017 15:17:32  1489677452000
  • This query returns correct result only if you DB server runs on UTC time zone, otherwise it is wrong. In such case you have to convert `sysdate` to UTC first (or convert '1970-01-01 00:00:00 UTC' to local time) – Wernfried Domscheit Mar 17 '17 at 07:21
  • Webpage http://www.timestampconvert.com/ makes correct conversion, it states that is expects local times. When I (living in Europe) enter Unix time '00' then I get result '1970-01-01 01:00:00' local time which is equal to '1970-01-01 00:00:00 UTC' – Wernfried Domscheit Mar 17 '17 at 07:34
  • @WernfriedDomscheit - I agree the website makes the correct conversion. My point (which I should perhaps make more explicit) is that the whole concept of converting an Oracle date to a Unix time is invalid. Unix time is "in a specific time zone". Oracle date is not. It makes absolutely no sense to convert an Oracle date based on server time zone; the Oracle date has nothing to do with the server time zone or any other time zone. What WOULD make sense is to convert between Unix time and Oracle timestamp with time zone (or with local time zone). –  Mar 17 '17 at 12:18
  • @WernfriedDomscheit - If you don't agree with the comment above, then **this** is what we don't agree on, not something else. (To repeat: perhaps you don't agree with my view, which is that conversions between Oracle dates, and timestamps without time zone, to Unix time, are meaningless to begin with.) –  Mar 17 '17 at 12:20
  • Yes, in fact `DATE` values (and thus also `SYSDATE`) do not have any timezone. However, in reality when you have times without any time zone information then it means (almost) always "local time". Due to that it makes sense to treat `DATE` values as local times and convert them accordingly. But that's just my opinion. – Wernfried Domscheit Mar 17 '17 at 14:18
  • @WernfriedDomscheit - what is not clear is the meaning of "local". Oracle certainly has at least two valid interpretations: local to the session and local to the server. Which is they have different flavors of timestamp with time zone. On the other hand: If I record the date "June 1, 2004" as the date of a significant event for my organization, it is very unlikely that any specific time zone was meant. Is it Eastern time or Pacific time (or UTC + 2 in Eastern Europe)? Nothing in the data will answer that question. It seems to me that such a date should be converted with no time zone assumed. –  Mar 17 '17 at 15:08
0

This should work:

SELECT (COLUMN_WITH_ORACLE_DATE_VALUE - TO_DATE('1970-01-01', 'YYYY-MM-DD')) * 86400000 FROM DUAL;

example:

SELECT (TO_DATE('02/02/2017','MM/DD/YYYY') - TO_DATE('01/01/1970', 'MM/DD/YYYY')) * 86400000 FROM DUAL;
morb1d
  • 143
  • 5