convert datetime to timestamp
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 46
- Joined: Tue Aug 10, 2004 11:07 am
- Location: Mclean VA
convert datetime to timestamp
Hi All,
Can any one of you let me know how to convert the datetime to timestamp in parallel extender jobs.
Thanks in Advance
Can any one of you let me know how to convert the datetime to timestamp in parallel extender jobs.
Thanks in Advance
Last edited by hondaccord94 on Thu Jul 28, 2005 9:01 pm, edited 1 time in total.
Re: convert datetime to timestamp
Hi,
since you r converting date to timestamp I will assume u will give some default time ... say 00:00:00
U can use this : StringToTimestamp(DateToString(DateColumn):"00:00:00")
this should solve the problem.
since you r converting date to timestamp I will assume u will give some default time ... say 00:00:00
U can use this : StringToTimestamp(DateToString(DateColumn):"00:00:00")
this should solve the problem.
-
- Participant
- Posts: 46
- Joined: Tue Aug 10, 2004 11:07 am
- Location: Mclean VA
Re: convert datetime to timestamp
Hi Sudarshan,
Thanks for the help,
But , I don't know whether I can use the one you mentioned in the buildop or do I need to write the c++ coding for this conversion.
If so, can you please throw some suggestions on how to proceed with this.
Thanks for the help,
But , I don't know whether I can use the one you mentioned in the buildop or do I need to write the c++ coding for this conversion.
If so, can you please throw some suggestions on how to proceed with this.
sudarshan wrote:Hi,
since you r converting date to timestamp I will assume u will give some default time ... say 00:00:00
U can use this : StringToTimestamp(DateToString(DateColumn):"00:00:00")
this should solve the problem.
-
- Participant
- Posts: 46
- Joined: Tue Aug 10, 2004 11:07 am
- Location: Mclean VA
Re: convert datetime to timestamp
Hi Sudarshan,
Thanks for the help,
But , I don't know whether I can use the one you mentioned in the buildop or do I need to write the c++ coding for this conversion.
If so, can you please throw some suggestions on how to proceed with this.
Thanks for the help,
But , I don't know whether I can use the one you mentioned in the buildop or do I need to write the c++ coding for this conversion.
If so, can you please throw some suggestions on how to proceed with this.
sudarshan wrote:Hi,
since you r converting date to timestamp I will assume u will give some default time ... say 00:00:00
U can use this : StringToTimestamp(DateToString(DateColumn):"00:00:00")
this should solve the problem.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: convert datetime to timestamp
Hi,
There is a "TimestampFromDateTime function in the parallel extender.. Its available in the "DateandTime" set of options in a parallel transformer.
Regards,
The Bird
There is a "TimestampFromDateTime function in the parallel extender.. Its available in the "DateandTime" set of options in a parallel transformer.
Regards,
The Bird
hondaccord94 wrote:Hi All,
Can any one of you let me know how to convert the datetime to timestamp in parallel extender jobs.
Thanks in Advance
Hello friends!
I have date and time converted to to TimeStamp with TimestampFromDateTime(%date%,%time%). This is StartDate/Time of some event! And I have another parameter - duration. So I need to calculate EndDate/Time of my event! How can I do this? If I do like this: StartDate/Time(this is TimeStamp) + Duration(this is integer) then it equals duration. But I need to calculate EndDate/Time.
Thank you!
I have date and time converted to to TimeStamp with TimestampFromDateTime(%date%,%time%). This is StartDate/Time of some event! And I have another parameter - duration. So I need to calculate EndDate/Time of my event! How can I do this? If I do like this: StartDate/Time(this is TimeStamp) + Duration(this is integer) then it equals duration. But I need to calculate EndDate/Time.
Thank you!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What is the unit of measurement of duration? Seconds? Days? You need to know this before you can perform arithmetic with dates. Search the forum (or the Orchestrate manuals) for the APT_Date class and the various constructors available with it.
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.
-
- Participant
- Posts: 46
- Joined: Tue Aug 10, 2004 11:07 am
- Location: Mclean VA
If you want to do this in a buildop, there are a few different options. If you can get the datetime into a string format, the set function will work. The definition is:
Another possibility is to load 2 variables with the date and time components of your source, defined as APT_Date and APT_Time, respectively. Then create a 3rd variable as APT_TimeStamp. Here's the functions to load it:
All of these functions are documented in the PX Parallel Jobs doc or you can use the actual header file in <AscentialRoot>/DataStage/PXEngine/include/apt_util/time.h
Hope this helps!
Brad.
Code: Select all
/** Sets the timestamp object's state
by parsing the supplied string, using an optional format string.
@note
If the string contains an invalid or unparseable time value,
the timestamp object is set to an invalid value.
@param timeStampString A character string containing a timestamp value
for parsing. (Does not have to be null-terminated.)
@param formatString Optional, format string used to parse the
timeStampString argument (see discussion of timestamp
format strings in the class documentation).
If formatString is unspecified, then one of two
default formats will be used, depending on whether
or not the timestamp object has microsecond resolution.
*/
void set(const APT_UString& timeStampString,
const APT_UString& formatString = APT_UString(""));
Code: Select all
APT_Date myDate;
APT_Time myTime;
/* load myDate and myTime from your datetime source field
-- I don't know how your are pulling in your datetime, so I'll
leave this part up to you... */
/* Load myTmsp var. The last parm indicates if your timestamp
has micros - set to either true or false. */
APT_TimeStamp myTmsp(myDate, myTime, true);
/* Another option */
APT_TimeStamp myTmsp2;
myTmsp.setDate(myDate);
myTmsp.setTime(myTime);
/* check if your timestamp is valid and set output field */
if (myTmsp.isValid) {
out.my_tmsp_field = myTmsp;
} else {
out.my_tmsp_field_setnull();
}
Hope this helps!
Brad.
-
- Participant
- Posts: 46
- Joined: Tue Aug 10, 2004 11:07 am
- Location: Mclean VA
Hi Brad,
Thanks for the information, but can you please tell me how to use to the set function, and also can you please let me know where to find these functions.
My requirement is I have the data coming in a string format (datetime datatype )and I need to convert that to timestamp.
Thanks for the information, but can you please tell me how to use to the set function, and also can you please let me know where to find these functions.
My requirement is I have the data coming in a string format (datetime datatype )and I need to convert that to timestamp.
bcarlson wrote:If you want to do this in a buildop, there are a few different options. If you can get the datetime into a string format, the set function will work. The definition is:
Another possibility is to load 2 variables with the date and time components of your source, defined as APT_Date and APT_Time, respectively. Then create a 3rd variable as APT_TimeStamp. Here's the functions to load it:Code: Select all
/** Sets the timestamp object's state by parsing the supplied string, using an optional format string. @note If the string contains an invalid or unparseable time value, the timestamp object is set to an invalid value. @param timeStampString A character string containing a timestamp value for parsing. (Does not have to be null-terminated.) @param formatString Optional, format string used to parse the timeStampString argument (see discussion of timestamp format strings in the class documentation). If formatString is unspecified, then one of two default formats will be used, depending on whether or not the timestamp object has microsecond resolution. */ void set(const APT_UString& timeStampString, const APT_UString& formatString = APT_UString(""));
All of these functions are documented in the PX Parallel Jobs doc or you can use the actual header file in <AscentialRoot>/DataStage/PXEngine/include/apt_util/time.hCode: Select all
APT_Date myDate; APT_Time myTime; /* load myDate and myTime from your datetime source field -- I don't know how your are pulling in your datetime, so I'll leave this part up to you... */ /* Load myTmsp var. The last parm indicates if your timestamp has micros - set to either true or false. */ APT_TimeStamp myTmsp(myDate, myTime, true); /* Another option */ APT_TimeStamp myTmsp2; myTmsp.setDate(myDate); myTmsp.setTime(myTime); /* check if your timestamp is valid and set output field */ if (myTmsp.isValid) { out.my_tmsp_field = myTmsp; } else { out.my_tmsp_field_setnull(); }
Hope this helps!
Brad.
Okay, here's an example.
Datafile:
0001|2005-07-04 10:15:01.123456
0002|2005-07-04 10:25:02.123456
0003|2005-07-04 10:35:03.123456
0004|2005-07-04 10:45:04.123456
0005|2005-07-04 10:55:05.123456
Sequential Stage input schema:
Buildop uses the same schema as input, and the same for output, except a new field called myTmsp is added at the end (definition = myTmsp: nullable timestamp[microseconds];).
Buildop logic:
The whole job is seq -> buildop -> dataset
Contents of the dataset (generated with orchadmin dump -name mydataset.ds):
recid:1 datetimeString:2005-07-04 10:15:01.123456 myTmsp:2005-07-04 10:15:01.123456
recid:2 datetimeString:2005-07-04 10:25:02.123456 myTmsp:2005-07-04 10:25:02.123456
recid:3 datetimeString:2005-07-04 10:35:03.123456 myTmsp:2005-07-04 10:35:03.123456
recid:4 datetimeString:2005-07-04 10:45:04.123456 myTmsp:2005-07-04 10:45:04.123456
recid:5 datetimeString:2005-07-04 10:55:05.123456 myTmsp:2005-07-04 10:55:05.123456
As for finding these functions, I guess the documentation is really lacking - the PX doc has no mention of the buildop functions. You'll need to look in the header files themselves: <your Ascential root dir>/DataStage/PXEngine/include/apt_util/time.h
Hope this helps.
Brad.
Datafile:
0001|2005-07-04 10:15:01.123456
0002|2005-07-04 10:25:02.123456
0003|2005-07-04 10:35:03.123456
0004|2005-07-04 10:45:04.123456
0005|2005-07-04 10:55:05.123456
Sequential Stage input schema:
Code: Select all
-schema record
{final_delim=end, record_delim='\\n', delim='|', null_field=''}
(
recid:int32 {max_width=4};
datetimeString:nullable string[26] {max_width=50};
)
Buildop logic:
Code: Select all
out.recid = in.recid;
out.datetimeString = in.datetimeString;
// Example input date string is "2005-07-04 10:15:01.123456"
// Populate myTmsp from datetimeString using set() function
out.myTmsp.set(in.datetimeString, "%yyyy-%mm-%dd %hh:%nn:%ss.6");
Contents of the dataset (generated with orchadmin dump -name mydataset.ds):
recid:1 datetimeString:2005-07-04 10:15:01.123456 myTmsp:2005-07-04 10:15:01.123456
recid:2 datetimeString:2005-07-04 10:25:02.123456 myTmsp:2005-07-04 10:25:02.123456
recid:3 datetimeString:2005-07-04 10:35:03.123456 myTmsp:2005-07-04 10:35:03.123456
recid:4 datetimeString:2005-07-04 10:45:04.123456 myTmsp:2005-07-04 10:45:04.123456
recid:5 datetimeString:2005-07-04 10:55:05.123456 myTmsp:2005-07-04 10:55:05.123456
As for finding these functions, I guess the documentation is really lacking - the PX doc has no mention of the buildop functions. You'll need to look in the header files themselves: <your Ascential root dir>/DataStage/PXEngine/include/apt_util/time.h
Hope this helps.
Brad.