Time and TimeStamp with microseconds
Moderators: chulett, rschirm, roy
Time and TimeStamp with microseconds
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?
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?
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
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.
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.
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.
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.
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
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
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).
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).
I needed my microseconds now, so I added an external routine for the purpose:
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
- /* 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;
}
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 ![Smile :)](./images/smilies/icon_smile.gif)
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.
![Smile :)](./images/smilies/icon_smile.gif)
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.
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.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. ...
Another questionvijayrc wrote: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.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. ...
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...