Change the TimeStamp(WST) to Timestamp(Central)

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
DSkkk
Charter Member
Charter Member
Posts: 70
Joined: Fri Nov 05, 2004 1:10 pm

Change the TimeStamp(WST) to Timestamp(Central)

Post by DSkkk »

hi i am getting data having timestamps from the sources in the west coast.
i want to change the entire timesatamp column adding an hour to the timestamp and converting it into the central. can anyone help me doing this.
Thanks in advance.
g.kiran
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Your timestamp inside DataStage is a string. You can decompose it with substring techniques, the Field() function, and lots of other ways. Manipulate the pieces and re-assemble.
A more robust solution will convert to internal format, adjust, and convert back to external format.

Code: Select all

intDate = Iconv(Left(TheTimeStamp,10), "DYMD")
intTime = Iconv(Right(TheTimestame,8), "MTS")
newTime = intTime + 3600
If newTime > 86400
Then
   intDate += 1
   newTime -= 86400
End
newDate = Oconv(intDate, "D-YMD[4,2,2]")
newTime = Oconv(newTime, "MTS:")
Ans = newDate : " " : newTime
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DSkkk
Charter Member
Charter Member
Posts: 70
Joined: Fri Nov 05, 2004 1:10 pm

Post by DSkkk »

thanks Ray!
but i think we have to take care of the enf of month and the leap years too. right!
g.kiran
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Ray's code will take care of leap years and month transitions.

-Arnd.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Clever, innit?

What's happening is that the timestamp is being converted into "seconds since a certain zero", an hour (3600 seconds) added, and that converted back into a timestamp.

The Iconv/Oconv functions are completely cognizant about the calendar, including the 11 days that the Pope removed in 1582 (see this site for example) to create the "Gregorian" calendar and various other anomalies (like years divisible by 100 not being leap years unless divisible by 400).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply