How to find the max of a Timestamp field

saini · Post by **saini** » Wed Jul 16, 2008 11:50 am

Hi

I have a requirement to find the max of a column. Its datatype is Timestamp.

I am trying to get the max of that column through aggregator Stage.

I get the following error:
Aggregator_94: Error when checking operator: When binding input interface field "ADJUST_DT" to field "ADJUST_DT": No default type conversion from type "timestamp" to type "dfloat". [api/interface_rep.C:803]

Please suggest

Thanks!!!

crouse · Post by **crouse** » Wed Jul 16, 2008 12:17 pm

Use the TimetFromTimestamp function to convert the timestamp to a number, then run through agg to get max, then use TimestampFromTimet to convert it back to the timestamp. Need to mess with conversion using DecimalToDfloat, I think, in order avoid a warning at runtime, since the agg expects the column to be dfloat. Or just live with the warning.

-Craig

saini · Post by **saini** » Wed Jul 16, 2008 12:24 pm

Is this function in the Transformer Stage as I am unable to find it

crouse wrote:Use the TimetFromTimestamp function to convert the timestamp to a number, then run through agg to get max, then use TimestampFromTimet to convert it back to the timestamp. Need to mess with conversion using DecimalToDfloat, I think, in order avoid a warning at runtime, since the agg expects the column to be dfloat. Or just live with the warning.

-Craig

pneumalin · Post by **pneumalin** » Wed Jul 16, 2008 12:46 pm

Yes, it's in Transformer Stage. But ensure you are using PX Transformer, Basic Transformer doesn't have this function.

blitz76 · Post by **blitz76** » Fri Dec 05, 2008 3:07 pm

Hi
I have been having issues with these functions for one date that we use to depect infinity.
This date is 2199-02-03 00:00:00 (YYYY-MM-DD).

Here is the issue.
After the aggregation, when i try to convert back the value, it converts to some other date.

TimetFromTimestamp('2199-02-03 00:00:00') gives -1360500992
And TimestampFromTimet(-1360500992) gives 1926-11-22 11:03:28 !!!!!!!!!!!!!

Did anybody face this issue before?.Would apprciate any inputs.

Regs
Ameet

kandyshandy · Post by **kandyshandy** » Fri Dec 05, 2008 3:19 pm

Try without converting timestamp to time format. In aggregator, you can add a subproperty "preserve type" to TRUE.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 05, 2008 3:19 pm

I suspect you're using Integer as the data type and getting overflow. A date in the future ought not to generate a negative timet. Try using dfloat as the data type.

bikan · Post by **bikan** » Fri Jan 09, 2009 2:52 am

Hi Gurus,

Sorry for hacking the thread, but I thought it would be easier for everyone to relate to my problem.

I also have to find MAX of a timestamp variable. I used timetfromtimestamp function, as suggested, to calculate the MAX value in aggregator.

My input timestamp field contains microseconds.

When I convert the output of aggregator to timestamp using timestamptotimet function, the microseconds data is lost.

Is there any other way to find MAX of timestamp variable and retain
microsecond data?

Thank you in advance.

priyadarshikunal · Post by **priyadarshikunal** » Fri Jan 09, 2009 12:02 pm

bikan wrote:Hi Gurus,

Sorry for hacking the thread, but I thought it would be easier for everyone to relate to my problem.

I also have to find MAX of a timestamp variable. I used timetfromtimestamp function, as suggested, to calculate the MAX value in aggregator.

My input timestamp field contains microseconds.

When I convert the output of aggregator to timestamp using timestamptotimet function, the microseconds data is lost.

Is there any other way to find MAX of timestamp variable and retain
microsecond data?

Thank you in advance.

instead to hacking this thread if you would have used the reply given by kandyshandy in this post itself.

you can use the sub property preserve type and setting it to yes will give you the desired output.