Conversion of Time from EST to GMT.

kollurianu · Post by **kollurianu** » Thu Jun 30, 2011 3:07 pm

Hi All,

Datastage server time is EST and in the jobs we want to insert GMT times..
so how do we do the conversion , taking care of the day light savings ?

Any thoughts greatly appreciated.

Thank you all.

mhester · Post by **mhester** » Thu Jun 30, 2011 4:24 pm

I would start with looking at the Unix TZ environment variable and its usage.

Then to avoid having all of your times GMT (exported say in your dsenv) I would write a simple C routine that you could call from your job to return a gmt timestamp when you need it. I have included an example that may/may not work for you

Code: Select all

char *pfLocal2GMTimestamp(char *localTimeStr)
{
    struct tm time_str;
    long int fraction;
    char daybuf[20];
    time_t timeval;
    char buffer[24];
    char *returnBuffer;
    returnBuffer=(char *)malloc(30);

    sscanf(localTimeStr,"%4d-%2d-%2d %2d:%2d:%2d.%6ld",&time_str.tm_year,&time_str.tm_mon,&time_str.tm_mday,&time_str.tm_hour,&time_str.tm_min,&time_str.tm_sec,&fraction);
    time_str.tm_year = time_str.tm_year - 1900;
    time_str.tm_mon = time_str.tm_mon - 1;
    time_str.tm_isdst = -1;
    timeval=mktime(&time_str);

    strftime(buffer,30,"%Y-%m-%d %T.",gmtime(&timeval));

    // add microseconds
    sprintf(returnBuffer, "%s%06ld",buffer,fraction);

    return returnBuffer;
}

ray.wurlod · Post by **ray.wurlod** » Thu Jun 30, 2011 4:50 pm

In an expression in a job sequence (for example in a User Variables activity) you could use the System(99) function, which returns the number of seconds since midnight GMT on January 1, 1970, then use Oconv() and Time() functions to convert that to current.

kollurianu · Post by **kollurianu** » Thu Jun 30, 2011 5:19 pm

Hi Ray ,

Thanks for your response.. but I am not able to view your full content of the post , though I renewed my premium membership account .. so could, have informing the Dxchange support team , but not fixed so far.

could somebody post the message for me.

Thanks for your help.

kollurianu · Post by **kollurianu** » Thu Jun 30, 2011 7:48 pm

Hi Ray ,
Thanks for your response..but I didn't find the System(99) function in the repository.. In which category of repository can we find this function ?
Any inputs greatly appreciated.

Thank you..

kollurianu · Post by **kollurianu** » Thu Jun 30, 2011 8:14 pm

Hi Ray,

Could you please give the exact syntax from time conversion from seconds to datetimestamp format ?

Any inputs greatly appreciated.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 30, 2011 9:49 pm

Code: Select all

uvGMTSecs  <--  System(99)
uvDays  <--  Int(uvGMTSecs / 86400)
uvSecs  <--  Mod(uvGMTSecs, 86400)
uvDate  <--  Oconv(uvDays + 732, "D-YMD")
uvTime  <--  Oconv(uvSecs, "MTS")
uvTimestamp  <--  uvDate : " " : uvTime

Generalize from the above. You couldn't, for example, do all of that in a single User Variables activity (because you can't refer to variables in from the same activity). 732 is the number of days between the DataStage internal day zero and 01/01/1970.

That will get you pretty close. It's out of my head - I'm currently preparing a new installation, so have no DataStage software available.

kollurianu · Post by **kollurianu** » Fri Jul 01, 2011 7:56 am

Thanks Ray for your Response..

But System(99) function , I am not able to find in the repository.

When I am trying to use that function in UV stage .. it says Variable "System" not defined.

Any inputs greatly appreciated.

Could someone post Ray's message again , because I am not able to view the full content.

Thank you all.

chulett · Post by **chulett** » Fri Jul 01, 2011 8:53 am

Use that function in a routine.

kollurianu · Post by **kollurianu** » Fri Jul 01, 2011 10:42 am

Thanks for your response Craig ..

You mean to call System(99) function in a Routine activity stage right.?
If so inorder to call System(99) function in a Routine activity stage I got to browse thru the repository path .. so for that I need the location of the function to use in Routine activity stage.

Any inputs greatly appreciated.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 01, 2011 6:34 pm

The System() function is not in the Repository - it is internal to the DataStage BASIC language itself.

I assume, from other threads, that your premium membership is now operational again and that you can read the entirety of my earlier post.

It does not require a routine at all, but shows an approach using user variables. But you could, of course, do the same in a routine.

mhester · Post by **mhester** » Fri Jul 01, 2011 6:51 pm

I am going to go out on a limb here and suggest that you do not write a BASIC routine to do this. You will have essentially tied yourself to either a UV stage in a sequencer or implementing a BASIC transform in a parallel job which is generally (won't say never, but want to) a good idea.

If you write a parallel routine then you can use that within a job wherever you might need it. You may have timestamp columns in your data that require conversion - the BASIC routine will not do that for you unless you implement a specific way.

Make every attempt to use the parallel palette and all that is there rather than reverting to server constructs. There is nothing that can be done in a server job or routine that cannot be done in the parallel palette.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 01, 2011 8:44 pm

Brave call.