time stamp

arshi · Post by **arshi** » Mon May 26, 2008 3:42 am

Hi,

How to develop the logic ,I have a table with columns (EmpNo,Ename,Join Time,DeptNo).I have to sum up all the employees join timein all the departments.This Jointime column datatype is Timestamp.

regards,
Arshi

ArndW · Post by **ArndW** » Mon May 26, 2008 3:49 am

You can do it with simple SQL directly in the SELECT statement, or use the aggregator stage in DataStage to do this.

arshi · Post by **arshi** » Mon May 26, 2008 8:09 am

Hi,

I am unable to get through Aggregator because data is in timestamp format.So,unable to add.

Regards,
Arshi

sachin1 · Post by **sachin1** » Mon May 26, 2008 8:37 am

change the datatype for timestamp column to varchar it will work.

vivekgadwal · Post by **vivekgadwal** » Mon May 26, 2008 10:25 am

sachin1 wrote:change the datatype for timestamp column to varchar it will work.

Remove the delimiters and spaces between the timestamps (using "Field" fn.) and then pass them into aggregator stage. (Ex: '2008-05-26 00:00:00' should be represented as '20080526000000').

Minhajuddin · Post by **Minhajuddin** » Mon May 26, 2008 10:31 am

I am just curious here... What kind of a situation would demand a "sum of timestamps"

Anyway, I think changing it to varchar, or removing the colons is not gonna work (My assumption here is that you want to do sum the dates as "dates" NOT as "integers" ).

What are you going to do after you aggregate all the dates? join them back to the original flow? if yes, how do you want to join it? or do you want just a single row with the aggregated date?

ray.wurlod · Post by **ray.wurlod** » Mon May 26, 2008 3:42 pm

You simply can not sum dates or timestamps. You need to derive another field, such as length of service, and sum that.