Time and TimeStamp with microseconds

ArndW · Post by **ArndW** » Fri Oct 14, 2005 8:59 am

I have a job which puts together a Varchar(32) string variable that contains a timestamp in the form "2005-01-01 12:13:14.567891".

When I move this to a TimeStamp defined column (extended is switched on) using the StringToTimestamp(StageVar,"%yyyy-%mm-%dd %hh:%nn:%ss.6") it converts correctly but strips out the milliseconds portion. The same thing happens using a Time value and the StringToTime() function.

I have tried numerous things to get this value converted with milliseconds, but nothing will work. The 6 digits of millisecond values are always replaced with 000000.

- changed the default timestamp in the job properties to include milliseconds {only done with difficulty, as there seems to be an issue with the non-default values}
- changed the attributes of the target column to include milliseconds in the format
- searched for a pertinent $APT variable without luck.

So far the only thing that has worked is writing this value as the varchar() to a sequential file and then reading this in another sequential job with a type time timestamp.microseconds and specifying an explicit format for the column. But this cannot be the correct solution.

My guess at the moment is that the StringToTime[stamp] functions do not actually convert the milliseconds but ignore them.

Has anyone successfully used either of these functions to generate a time or timestamp with milliseconds?

roy · Post by **roy** » Sun Oct 16, 2005 11:11 am

Hi Arnd,
Alas I'll have access to PX around november not sooner so testing would be dificult.
Did you try to play with the timestamp format at the job level properties in the Defaults tab your timestamp usually lacks the fractions of seconds.

IHTH,

ArndW · Post by **ArndW** » Sun Oct 16, 2005 11:15 am

Roy,

yes I did play with that setting. Oddly, when you removed the ticked "use project default" checkbox the displayed default value causes a runtime error - it somehow doesn't like the embedded space in the default "%yyyy-%mm-%dd %hh:%nn:%ss"! I was too busy trying to get the job working to bother submitting that as a bug, but I'll get around to that next week. I replaced this with a test format string picture of "%yyyy-%mm-%dd!%hh:%nn:%ss.6" which compiled & ran but alas the resultant timestamp still had 6 zeroes instead of the expected microsecond values.

clshore · Post by **clshore** » Tue Oct 18, 2005 6:42 pm

Arnd,
Did you ever get the microseconds to show?

I'd much rather use a DataStage built-in to get usec, than roll custom code with gettimeofday() etc.

Carter

ArndW · Post by **ArndW** » Wed Oct 19, 2005 2:11 am

Hello Carter,

the case is open with Ascential, but now that you brought the subject back up I realized that I haven't followed through on this.

So far I haven't gotten it to work. The temporary workaround in use is that the developer has written the data to a flat file as a string (he's put the timestamp with microseconds together using normal string functions) and then reads it with a declaration of "timestamp(extended)". This is not a great solution, as it single-threads the parallel job; but since each column's datatype is written as part of a DataSet's schema we can't use that.

Even using the different functions in a modify stage hasn't worked, those pesky milliseconds are always replaced with .000000 instead of the 6 places that are required. Reading an extended value from DB/2 is no problem, it is just the string -> time or timestamp that is causing problems.

Madhusv · Post by **Madhusv** » Thu Oct 20, 2005 5:41 am

Hi ALL,

I am also facing Problem with Timestamp.

The problem is :
In Oracle table the datatype of one field is Timestamp which is not interprted by Datastage Timestamp.

The error is as below.

main_program: Internal Error: (colDesc):orautils.C: 1714: getColDesc failed.
Traceback: Could not obtain stack trace; check that 'dbx' and 'sed' are installed and on your PATH

Regards,
Madhu

ArndW · Post by **ArndW** » Thu Oct 20, 2005 6:55 am

Madhusv,

this is a different problem altogether. Your error is (I think) occurring when doing a view-data or an import of metadata. I assume that you are getting other data types loaded in correctly? Remember that an oracle timestamp is different from a DB/2 timestamp. Please post your response in a new thread, since this one is neither closed nor of general interest to most PXers (so people won't look at it and won't see if they can help with your problem).

clshore · Post by **clshore** » Thu Oct 20, 2005 11:31 am

I needed my microseconds now, so I added an external routine for the purpose:

/* getSysUsec.c gets sys seconds and usecs, formats as "%d.%06d", returns string */

#include <unistd.h>
#include <stdio.h>
#include <time.h>
#include <sys/time.h>

char * getSysUsec(){
struct timeval time;
char result[30];

if (gettimeofday(&(time), NULL) != 0) {
perror("getSysUsec");}

sprintf(result, "%d.%06d", time.tv_sec, time.tv_usec);

return result;
}

After you compile it to *.o or *.so, create a new Routines entry, and point it to the *.o or *.so.

This one returns the UNIX epoch seconds and microseconds as a string (char*), because that's what I wanted, change it as needed.

Now, getSysUsec() shows on the dropdown list in transformations, so I can say:

DSJobName : "|Transformer_7|" : getSysUsec()

and get:

"HelloJobPar2|Transformer_7|1129831508.432819"
"HelloJobPar2|Transformer_7|1129831508.432891"
"HelloJobPar2|Transformer_7|1129831508.432907"

Carter

ArndW · Post by **ArndW** » Thu Oct 20, 2005 11:39 am

Carter,

Thanks!! I'll install it at work tomorrow and use that until the answer comes from IBM/Ascential.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 20, 2005 4:29 pm

I wonder if their answer will include Carter's routine? Ya shoulda put a copyright notice into it before "they" do.

ArndW · Post by **ArndW** » Fri Oct 21, 2005 2:27 am

I've been resisting the beck and call of writing c++ externals for PX for so long, but this routine is so simple that even I should be able to bind it in within a week's worth of work. So even if I get a solution from Ascential I'll still use Carter's code for my first attempt at this esoteric art

I have received a PM that someone else has successfully done this, so I am hopeful that it might still be just a parameter/environment or job setting. Something as fundamental at microsecond conversions from strings has got to work.

ArndW · Post by **ArndW** » Tue Oct 25, 2005 12:52 am

It would seem that this is a bug in DS, Ascential is working on a fix and/or workaround. I'll post whatever comes through from support here.

vijayrc · Post by **vijayrc** » Sat May 19, 2007 5:12 pm

ArndW wrote:It would seem that this is a bug in DS, Ascential is working on a fix and/or workaround. I'll post whatever comes through from support here. ...

Has this been fixed in DS 7.5.2 ? I don't get the microseconds with the options mentioned in the manual %yyyy-%mm-%dd %hh:%nn:%ss.x where x is a no between 0 and 6 for the microseconds. I have the Job Parameter override for Timestamp to be %yyyy-%mm-%dd %hh:%nn:%ss.6 Compile goes thru, but the job fails. I'll post the message later.

vijayrc · Post by **vijayrc** » Mon May 21, 2007 3:12 pm

vijayrc wrote:
ArndW wrote:It would seem that this is a bug in DS, Ascential is working on a fix and/or workaround. I'll post whatever comes through from support here. ...
Has this been fixed in DS 7.5.2 ? I don't get the microseconds with the options mentioned in the manual %yyyy-%mm-%dd %hh:%nn:%ss.x where x is a no between 0 and 6 for the microseconds. I have the Job Parameter override for Timestamp to be %yyyy-%mm-%dd %hh:%nn:%ss.6 Compile goes thru, but the job fails. I'll post the message later.

Another question
Now my Timestamp defaults to %yyyy-%mm-%dd.%hh:%nn:%ss and instead of ":" in time, I want a '.'
So I change the Job Properties of the job to be %yyyy-%mm-%dd.%hh.%nn.%ss but I still end up getting ":" instead of "."
eg.2007-05-21 17:07:17 What I get
2007-05-21 17.07.17 What I need
Any suggestions on where I'm wrong...

chulett · Post by **chulett** » Mon May 21, 2007 3:24 pm

I doubt you're "going wrong" anywhere. Just seems like you need to translate the colons into periods after you've gotten the timestamp, if you really need it in that format.