Converting a timestamp data type to an unix Oracle timestamp

Question

I have a timestamp data type in a database with 24-JuL-11 10.45.00.000000000 AM format and want it converted to a unix timestamp, how can I get it?

Answer 1

This question is pretty much the opposite of converting Unixtime to Datetime SQL (Oracle).

As Justin Cave says:

There are no built-in functions. But it is relatively easy to write. Since Unix timestamp is the number of seconds since January 1, 1970

Since subtracting one date from another results in the number of days between them, you can do something like:

create or replace function date_to_unix_ts( PDate in date ) return number is l_unix_ts number; begin l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24; return l_unix_ts; end; 

Since in seconds since 1970, the number of fractional seconds does not matter. You can still call it with the timestamp data type, though ...

 SQL> select date_to_unix_ts(systimestamp) from dual; DATE_TO_UNIX_TS(SYSTIMESTAMP) ----------------------------- 1345801660 

---

Sorry for your comment, but I don't see this behavior:

 SQL> with the_dates as ( 2 select to_date('08-mar-12 01:00:00 am', 'dd-mon-yy hh:mi:ss am') as dt 3 from dual 4 union all 5 select to_date('08-mar-12', 'dd-mon-yy') 6 from dual ) 7 select date_to_unix_ts(dt) 8 from the_dates 9 ; DATE_TO_UNIX_TS(DT) ------------------- 1331168400 1331164800 SQL> 

There are 3600 seconds of difference, i.e. 1 hour.

Answer 2

I understand that the answer has already been accepted, but I think it should be clear that the function in this answer does not take into account the time zone offset traveled in the date. The correct Unix timestamp must be calculated in GMT (+0). The Oracle to_date function assumes that the to_date date is in the local time zone, unless otherwise specified. This problem is compounded by the fact that daylight saving time is a real thing. I crossed this issue with the following function:

 create or replace function unix_time_from_date ( in_date in date, in_src_tz in varchar2 default 'America/New_York' ) return integer as ut integer := 0; tz varchar2(8) := ''; tz_date timestamp with time zone; tz_stmt varchar2(255); begin /** * This function is used to convert an Oracle DATE (local timezone) to a Unix timestamp (UTC). * * @author James Sumners * @date 01 February 2012 * * @param in_date An Oracle DATE to convert. It is assumed that this date will be in the local timezone. * @param in_src_tz Indicates the time zone of the in_date parameter. * * @return integer */ -- Get the current timezone abbreviation (stupid DST) tz_stmt := 'select systimestamp at time zone ''' || in_src_tz || ''' from dual'; execute immediate tz_stmt into tz_date; select extract(timezone_abbr from tz_date) into tz from dual; -- Get the Unix timestamp select (new_time(in_date, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (86400) into ut from dual; return ut; end unix_time_from_date; 

I have some helper functions, unix_time and unix_time_to_date , available at http://jrfom.com/2012/02/10/oracle-and-unix-timestamps-revisited/ . I can not believe that Oracle brought everything to 11g without their implementation.

Answer 3

for date:

  FUNCTION date_to_unix (p_date date,in_src_tz in varchar2 default 'Europe/Kiev') return number is begin return round((cast((FROM_TZ(CAST(p_date as timestamp), in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60)); end; 

for timestamp:

 FUNCTION timestamp_to_unix (p_time timestamp,in_src_tz in varchar2 default 'Europe/Kiev') return number is begin return round((cast((FROM_TZ(p_time, in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60)); end; 

Answer 4

I use the following method, which is slightly different from other answers in that it uses the sessiontimezone() function to get the date correctly

  select ( cast((FROM_TZ(CAST(in_date as timestamp), sessiontimezone) at time zone 'GMT') as date) -- in_date cast do GMT - TO_DATE('01.01.1970','dd.mm.yyyy') -- minus unix start date ) * 86400000 -- times miliseconds in day from dual; 

Answer 5

Here is what I came up with:

 select substr(extract(day from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 24 * 60 * 60 + extract(hour from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 * 60 + extract(minute from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 + trunc(extract(second from (n.origstamp - timestamp '1970-01-01 00:00:00')),0),0,15) TimeStamp from tablename; 

Fwiw

Answer 6

 SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL 

Answer 7

For time conversion of Oracle and Unix I use these functions. They consider your current time zone. You should also add the DETERMINISTIC keyword, for example, if you want to use such a function in a function-based index. The conversion between DATE and TIMESTAMP must be done implicitly by Oracle.

 FUNCTION Timestamp2UnixTime(theTimestamp IN TIMESTAMP, timezone IN VARCHAR2 DEFAULT SESSIONTIMEZONE) RETURN NUMBER DETERMINISTIC IS timestampUTC TIMESTAMP; theInterval INTERVAL DAY(9) TO SECOND; epoche NUMBER; BEGIN timestampUTC := FROM_TZ(theTimestamp, timezone) AT TIME ZONE 'UTC'; theInterval := TO_DSINTERVAL(timestampUTC - TIMESTAMP '1970-01-01 00:00:00'); epoche := EXTRACT(DAY FROM theInterval)*24*60*60 + EXTRACT(HOUR FROM theInterval)*60*60 + EXTRACT(MINUTE FROM theInterval)*60 + EXTRACT(SECOND FROM theInterval); RETURN ROUND(epoche); END Timestamp2UnixTime; FUNCTION UnixTime2Timestamp(UnixTime IN NUMBER) RETURN TIMESTAMP DETERMINISTIC IS BEGIN RETURN (TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixTime * INTERVAL '1' SECOND) AT LOCAL; END UnixTime2Timestamp; 

Answer 8

 SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') dt, round((sysdate - to_date('19700101 000000', 'YYYYMMDD HH24MISS'))*86400) as udt FROM dual;