How to find the max of a Timestamp field
Moderators: chulett, rschirm, roy
How to find the max of a Timestamp field
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!!!
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!!!
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
-Craig
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
Yes, it's in Transformer Stage. But ensure you are using PX Transformer, Basic Transformer doesn't have this function.
Pneuma Lin.
pneumalin@yahoo.com
pneumalin@yahoo.com
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
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
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
instead to hacking this thread if you would have used the reply given by kandyshandy in this post itself.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.
![Wink :wink:](./images/smilies/icon_wink.gif)
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)