Convert Unix Time to oracle date

aasaif · Post by **aasaif** » Thu Jan 29, 2009 3:37 pm

Does anyone know how to convert a unix time ("1201546826")
into an oracle date in datastage.

I know it can be done in the sql but i am passing this into a procedure that i cannot change right now.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 29, 2009 5:33 pm

Is this "UNIX time" present in data (a time_t), or simply being picked up from the operating system?

aasaif · Post by **aasaif** » Fri Jan 30, 2009 8:32 am

its being passed in the data

kduke · Post by **kduke** » Fri Jan 30, 2009 12:10 pm

I would substring it until it is close enough to use Oracle's to_date().

GSIDSXGrp · Post by **GSIDSXGrp** » Fri Jan 30, 2009 12:50 pm

The example looks like an Epoch "(00:00:00 UTC, January 1, 1970), measured in seconds", which is a time_t value.

Look at the function "TimestampFromTimet". Once you've the DS timestamp you may apply any of the Date/Time function.

aasaif · Post by **aasaif** » Fri Jan 30, 2009 1:40 pm

Where can i find this function TimestampFromTimet".

aasaif · Post by **aasaif** » Fri Jan 30, 2009 1:41 pm

Where can i find this function TimestampFromTimet".

GSIDSXGrp · Post by **GSIDSXGrp** » Fri Jan 30, 2009 2:25 pm

I may have mis-spoke. I was looking in the Parallel Job manual. I'm not sure if a Server function exists.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 30, 2009 2:34 pm

Check in the SDK transforms/routines for those that deal with 1970-based dates.

aasaif · Post by **aasaif** » Fri Jan 30, 2009 2:54 pm

Ok i am hoping i am close
DateDaysSince1970ToTimeStamp(1233260928)
ouputed this 85230926 00:00:00.000

so i am assuming this is another format i have to convert does it look familiar to anybody

ray.wurlod · Post by **ray.wurlod** » Fri Jan 30, 2009 9:05 pm

No, you will need to adapt it. Your number is in seconds, the function expects an argument in days. (There are, in general, 86400 seconds in a day, so a simple division will suffice.)

aasaif · Post by **aasaif** » Mon Feb 02, 2009 8:49 am

Thanks that helped i almost the only issue i see is
where do this forumla
DateDaysSince1970ToTimeStamp(1233260928 /86400)
I am getting 2009-01-28

however when i run this query in oracle sql

select to_date('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(1233260928,'SECOND')from dual
I am getting 2009-01-29

aasaif · Post by **aasaif** » Mon Feb 02, 2009 1:14 pm

I got it i built my own routine to get what i need thanks

aasaif · Post by **aasaif** » Mon Feb 02, 2009 1:15 pm

*************************************************************************
* Copyright (C) 2004, 1997-2003 Ascential Software Corporation. All Rights Reserved. *
* This code may be copied on condition that this copyright *
* notice is included as is in any code derived from this source. *
*************************************************************************
*
* Return current GMT / UTC time in YYYY-MM-DD HH:MM:SS.sss format (as used by
* other SDK routines and transforms).
*

*
* We use the SYSTEM(99) call to retrieve the number of seconds midnight
* since Jan 1st, 1970 GMT. and then compute the internal time format
* to the output conversion.
*

* Find the number of days since then.
InternalDate = DIV(Arg1,86400)

* Convert number of days to internal date format.
InternalDate += ICONV('01/01/1970','D4/DMY')

* Find seconds since midnight.
InternalTime = MOD(Arg1,86400)

* Now build the SDK timestamp string.
Ans = OCONV(InternalDate,'D4-YMD[4,2,2]'):' ':OCONV(InternalTime,'MTS'):'.000'