Adding an hour to a timestamp column

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
lateshj
Participant
Posts: 12
Joined: Wed Nov 05, 2003 11:54 pm

Adding an hour to a timestamp column

Post by lateshj »

Hi,
I need to add an hour to the value stored in a column of data type TimeStamp.

Could not find any direct function to do that ,

Could ne one guide me as to how to write such a code in datastage and use that in a transformer derivation

eg If the input file record contains 2003-12-04 23:55:00 then in the output column the value must be 2003-12-05 00:55:00

Tx,
Latesh
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Here's a post I did with logic for subtracting two timestamps:

viewtopic.php?t=85788


You can reference this to help you in adding your time. The piece of info you need is that DS BASIC converts time into the number of seconds since midnight. So, 12:00:01am is 1, and 11:59:59 is 86399. You'll have to check to see if adding the time rolls you past 86399, and then increment the day.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
clshore
Charter Member
Charter Member
Posts: 115
Joined: Tue Oct 21, 2003 11:45 am

Post by clshore »

What's your performance requirement?
You can use the C/C++ time functions and embed them into a custom Parallel Routine, or shell out to a custom UNIX command line utility written in C/C++, Perl, or whatever.
You're on UNIX, so convert the date/timestamp string to epoch seconds, add 3600, convert back to date/timestamp string, return the string.

Carter
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Whooa, I missed that it's Parallel. :oops:

:oops:


Note to self: Get more sleep.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
vzoubov
Participant
Posts: 28
Joined: Tue Feb 05, 2002 12:30 pm
Location: Boston, MA

Re: Adding an hour to a timestamp column

Post by vzoubov »

lateshj wrote:Hi,
I need to add an hour to the value stored in a column of data type TimeStamp.

Could not find any direct function to do that ,

Could ne one guide me as to how to write such a code in datastage and use that in a transformer derivation

eg If the input file record contains 2003-12-04 23:55:00 then in the output column the value must be 2003-12-05 00:55:00

Tx,
Latesh
The Orchestrate APT_TimeStamp class has the method addHours(). You can use it in a buildop.

Vitali.
lateshj
Participant
Posts: 12
Joined: Wed Nov 05, 2003 11:54 pm

Post by lateshj »

Hi ,
I was exploring the Transformer functions and this did work to an extent

TimestampFromSecondsSince(3600, "2003-12-04 23:55:00")
Gives the output as
2003-12-05 00:55:00

However this lead me to another error; I tried to add a stage variable of timestamp data type as second parameter; which fails

ERROR:TFCP 10:49:35(000) <transform> transform/func.C(1.4.2.6),141: Parsing parameters "GmtTime" for conversion "timestamp=timestamp_from_seconds_since[*******************](dfloat)": APT_Conversion_TimeStamp_SecondsSince: Parameter [GmtTime] does not parse as a timestamp

The input stage variable is built using the following function
GmtTime = TimestampFromDateTime(DSLink11.subscribe_date, DSLink11.subscribe_time)

Any ideas on this. :?:

I am little new at DS programming :( and need to still work out learning using parallel/server routines, wrapping ...buiild ops...
hence for the time being I better stick to transformer derivations

Tx
Latesh
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

Well, what do you know, you found a bug!

Basically, this function hates having an reference to the 2nd parameter.

I am unable to find ANY references to the official manuals for TimestampFromSecondsSince() function. It is found on the Orchestrate OEM documentation (timestamp_from_seconds_since). I have been able to repeat this issue with my PX 6.0.1. I tried a number of workarounds, including having a stage variable be used. No luck.

Anyone here with PX 7.0 that wanna test this? I can give you the DSX I created for this test.

In the meantime, file a report with Ascential Support. I will do the same on my side.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

Update:

It is a known issue with Ascential -

Summary of ecase 41383:
The DateFromDaysSince and DaysSinceFromDate functions do not take date columns as input. Jobs with date column inputs fail to compile. Jobs with hard coded dates will compile.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
bigpoppa
Participant
Posts: 190
Joined: Fri Feb 28, 2003 11:39 am

Adding an hour to a timestamp column

Post by bigpoppa »

If the timestamp column is fixed width, you could read it in a character string and just manipulate the characters that encode the hours.

- BP
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Update - Fixed in 7.0.1 - Was: Adding an hour to a timestamp

Post by Teej »

Update:

E.case 42158 that address the problem with the timestamp_from_date_time() should have a fix for 7.0.1.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
lateshj
Participant
Posts: 12
Joined: Wed Nov 05, 2003 11:54 pm

Post by lateshj »

Thanks Teej,
I also had a case opened with Support, not much help though. I did then implement my problem using a BuildOP..
Really enjoyed making one, My First One....

Adding the sample code for others reference.
It reads 2 files
1. Containing my input file records having the GMT Timestamp
2. A File containing a single record the start & end DayLight saving dates

Output
1. Input (1) with the BST based on input GMT & Daylight savings time

Definitions
# include "/opt/DataStage/Ascential/DataStage/PXEngine/include/apt_util/time.h"
# include "/opt/DataStage/Ascential/DataStage/PXEngine/include/apt_util/date.h"

Pre Loop
//Read the dates in the File 1 containing DayLight savings interval
readRecord ( 1);
APT_Date objDateD1 ;
objDateD1 = APT_Date(inDayLight.StartDate, "%mm-%dd-%yyyy") ;

APT_Date objDateD2 ;
objDateD2 = APT_Date(inDayLight.EndDate, "%mm-%dd-%yyyy") ;


Per Record Code
// Read the File 0
doTransfer ( 0 );

// Compute the time stamp into a object
APT_TimeStamp objTimeStamp ;
APT_Date objTimeStampDate ;

//Get the timestamp in GMT
objTimeStamp = indata.gmt_credit_purchase_date ;

objTimeStampDate = objTimeStamp.date() ;

//based on the DayLight Savings convert GMT to Brussels Time
if ( (objTimeStampDate >= objDateD1 ) && (objTimeStampDate <= objDateD2 ) )
objTimeStamp .addSeconds(7200);
else
objTimeStamp .addSeconds(3600);

result.bst_credit_purchase_date = objTimeStamp ;

// Transfer remaining data

writeRecord(0);

Any suggestions are welcome....

Tx
Latesh
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

Not bad! Great job integrating it using Orchestrate libraries. The solution I had in mind was a very C specific routine, which probably is not as optimized as your solution would be (although the difference would be very minor.)

Do keep in mind that once you get to 7.0.1, it is better to surrender the buildop and stick with that function. It is a pain in the arse to migrate buildops (you know, I probably haven't filed an enhancement request for buildops to be automatically generated when migrated... will do that tomorrow.) BuildOPS are one of the 'last resort' kind of thing, where no other reasonable solutions can be found.

Anyway, great job on that buildop. Thanks for sharing. :)

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
Post Reply