Page 1 of 1
Convert Unix Time to oracle date
Posted: Thu Jan 29, 2009 3:37 pm
by aasaif
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.
Posted: Thu Jan 29, 2009 5:33 pm
by ray.wurlod
Is this "UNIX time" present in data (a time_t), or simply being picked up from the operating system?
Posted: Fri Jan 30, 2009 8:32 am
by aasaif
its being passed in the data
Posted: Fri Jan 30, 2009 12:10 pm
by kduke
I would substring it until it is close enough to use Oracle's to_date().
Posted: Fri Jan 30, 2009 12:50 pm
by GSIDSXGrp
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.
Posted: Fri Jan 30, 2009 1:40 pm
by aasaif
Where can i find this function TimestampFromTimet".
Posted: Fri Jan 30, 2009 1:41 pm
by aasaif
Where can i find this function TimestampFromTimet".
Posted: Fri Jan 30, 2009 2:25 pm
by GSIDSXGrp
I may have mis-spoke. I was looking in the Parallel Job manual. I'm not sure if a Server function exists.
Posted: Fri Jan 30, 2009 2:34 pm
by ray.wurlod
Check in the SDK transforms/routines for those that deal with 1970-based dates.
Posted: Fri Jan 30, 2009 2:54 pm
by aasaif
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
Posted: Fri Jan 30, 2009 9:05 pm
by ray.wurlod
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.)
Posted: Mon Feb 02, 2009 8:49 am
by aasaif
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
Posted: Mon Feb 02, 2009 1:14 pm
by aasaif
I got it i built my own routine to get what i need thanks
Posted: Mon Feb 02, 2009 1:15 pm
by aasaif
*************************************************************************
* 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'