convert datetime to timestamp

hondaccord94 · Post by **hondaccord94** » Fri Jul 08, 2005 9:25 am

Hi All,
Can any one of you let me know how to convert the datetime to timestamp in parallel extender jobs.

Thanks in Advance

sudarshan · Post by **sudarshan** » Fri Jul 08, 2005 9:56 am

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.

hondaccord94 · Post by **hondaccord94** » Fri Jul 08, 2005 1:28 pm

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.

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.

hondaccord94 · Post by **hondaccord94** » Fri Jul 08, 2005 1:28 pm

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.

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.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 08, 2005 5:25 pm

You can use that expression directly in a Modify stage.

Madhav_M · Post by **Madhav_M** » Sat Jul 09, 2005 4:37 am

Just in the modify or Transformer stage...
"edit derivation" and and use the function suggested.

thebird · Post by **thebird** » Sun Jul 10, 2005 12:43 am

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

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

Semkaa · Post by **Semkaa** » Sun Jul 10, 2005 5:39 am

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!

ray.wurlod · Post by **ray.wurlod** » Sun Jul 10, 2005 3:34 pm

What is the unit of measurement of duration? Seconds? Days? You need to know this before you can perform arithmetic with dates.

ray.wurlod · Post by **ray.wurlod** » Sun Jul 10, 2005 3:35 pm

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.

Semkaa · Post by **Semkaa** » Mon Jul 11, 2005 12:21 am

Oh sorry I fogot about this. Duration is represented in seconds! I've searched forum but with no results

hondaccord94 · Post by **hondaccord94** » Mon Jul 11, 2005 8:45 am

Thanks Ray,Madhav,the bird,sudarshan for the information.

bcarlson · Post by **bcarlson** » Mon Jul 11, 2005 10:42 am

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:

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(""));

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

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();
}

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.

hondaccord94 · Post by **hondaccord94** » Wed Jul 13, 2005 8:42 am

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.

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:

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(""));

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

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();
}

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.

bcarlson · Post by **bcarlson** » Wed Jul 13, 2005 11:40 am

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:

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 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:

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");

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.

DSXchange

convert datetime to timestamp

convert datetime to timestamp

Re: convert datetime to timestamp

Re: convert datetime to timestamp

Re: convert datetime to timestamp

Re: convert datetime to timestamp