DSXchange

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

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.

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

Whooa, I missed that it's Parallel.




Note to self: Get more sleep.

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.

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

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.

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.

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

Update:

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

-T.J.

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

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.