subtract interval from timestamp

[knowledge]

Hi ,

we are getting time with 5 hrs added (zulu time) from EST time . I have to convert data to subtract 5 (or 4) from the source time to match to local time depending on daylight saving . in short I prepopulated time table with all dates with flag for daylight saving .

I have query which works fine except I would like to give interval automatic...not hardcoded .

.................

SELECT TO_CHAR(s.UNIT_NTFD_TMSP- INTERVAL '4' HOUR,'YYYY-MM-DD HH24:MI:SS')
FROM
reftable r , sourcetable s
WHERE trim(to_char(r.DATE_DTE,'yyyy-mm-dd')) =trim(to_char(s.UNIT_NTFD_TMSP,'yyyy-mm-dd') m

this works fine ......but i dont want to hardcode interval '4' I would like to calculate the value ........... if i use following query ...it gives me error , right parantheses missing .......



SELECT TO_CHAR(s.UNIT_NTFD_TMSP INTERVAL TRANSLATE(NVL(r.DYLGHT_SVNGS_CODE,'N'),'YN','45') HOUR,'YYYY-MM-DD HH24:MI:SS')
FROM
reftable r , sourcetable s
WHERE trim(to_char(r.DATE_DTE,'yyyy-mm-dd')) =trim(to_char(s.UNIT_NTFD_TMSP,'yyyy-mm-dd') m


pl suggest what can i use for interval..............

[ray.wurlod]

You might find it easier to convert the time within DataStage. Split the timestamp into date and time components, and perform the arithmetic. A small routine is probably easiest.

Code: Select all

FUNCTION AddHoursToTimestamp(TheTimestamp,Hours)
* Error handling has been omitted for clarity.
* I have assumed that the timestamp does not have fractional seconds.
TheDate = Field(TheTimestamp, " ", 1, 1)
TheTime = Field(TheTimestamp, " ", 2, 1)
intTheDate = Iconv(TheDate, "DYMD")
intTheTime = Iconv(TheTime, "MT")
intNewTime = intTheTime + 3600 * Hours
Loop While intNewTime >= 86400
   intNewTime -= 86400
   intTheDate += 1
Repeat
Loop While intNewTime < 0
   intNewTime += 86400
   intTheDate -= 1
Repeat
NewDate = Oconv(intTheDate, "D-YMD[4,2,2]")
NewTime = Oconv(intNewTime, "MTS:")
Ans = NewDate : " " : NewTime
RETURN(Ans)

[knowledge]

I am writing procedure to take care of this ,
but for more info :

date -(4/24) also works ...where 4 is the interval subtracted from hours..........

Thanks.

[ArndW]

No, that won't really work in DataStage but will work in Excel. Dates are stored as integers, not real numbers.

[knowledge]

no I tried in oracle , it works .............