Unable to fetch microseconds from timestamp
Moderators: chulett, rschirm, roy
Unable to fetch microseconds from timestamp
I am working on a job which is used for incremental loading depending upon the timestamp of the source records.
Now during execution it does not consider the microseconds.For eg: if the time stamp in source is 2006-08-25-01.02.42.764123 ; it executes the job taking the timestamp as 2006-08-25-01.02.42.000000 so more number of records get loaded in the target and also the data that gets loaded in the target column is 2006-08-25-01.02.42.000000.
I tried changing the format of timestamp for my project to 'yyyy-mm-hh-dd hh:mm:ss.SSSSSS' through administrator.Still the same problem persists.
My source and target stages are DB2/UDB API.
Now during execution it does not consider the microseconds.For eg: if the time stamp in source is 2006-08-25-01.02.42.764123 ; it executes the job taking the timestamp as 2006-08-25-01.02.42.000000 so more number of records get loaded in the target and also the data that gets loaded in the target column is 2006-08-25-01.02.42.000000.
I tried changing the format of timestamp for my project to 'yyyy-mm-hh-dd hh:mm:ss.SSSSSS' through administrator.Still the same problem persists.
My source and target stages are DB2/UDB API.
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ray!
I searched for it.There are indeed quite a few posts regarding this issue,but the issue has not been resolved so far,just a workaroung has been suggested..............to use varchar as datatype.
I tried writing in the database with varchar as data type.Its working.
Thank you and sorry for not searching it before and posting it under a new topic.
I searched for it.There are indeed quite a few posts regarding this issue,but the issue has not been resolved so far,just a workaroung has been suggested..............to use varchar as datatype.
I tried writing in the database with varchar as data type.Its working.
Thank you and sorry for not searching it before and posting it under a new topic.
Re: Unable to fetch microseconds from timestamp
Hi,
Extract as varchar and try convert function StringToTimestamp
Extract as varchar and try convert function StringToTimestamp
SAMEENA wrote:I am working on a job which is used for incremental loading depending upon the timestamp of the source records.
Now during execution it does not consider the microseconds.For eg: if the time stamp in source is 2006-08-25-01.02.42.764123 ; it executes the job taking the timestamp as 2006-08-25-01.02.42.000000 so more number of records get loaded in the target and also the data that gets loaded in the target column is 2006-08-25-01.02.42.000000.
I tried changing the format of timestamp for my project to 'yyyy-mm-hh-dd hh:mm:ss.SSSSSS' through administrator.Still the same problem persists.
My source and target stages are DB2/UDB API.
Syed Ibrahim Abdul Gani,
Senior DW Consultant,
Singapore
+65 97457387
Senior DW Consultant,
Singapore
+65 97457387
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The Transformer stage can still output a VarChar, then you use a downstream stage (another Transformer or a Modify stage) to convert the data type. Whether this will conserve the microseconds I am not sure, but it's the approach I'd investigate. My guess is that, if the timestamp format string includes microseconds, then they'd be handled properly by timestamp_from_string() function.
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.
Hi ,
When I keep format of timestamp as %yyyy-%mm-%dd %hh:%nn:%ss.6
its fetching microseconds but while writing in target which is DB2/UDB API stage ,it is truncating microseconds; even if I fetch microseconds separately and concatenate it .But I can populate it in a sequential file with data type as timestamp.So just wanted to know is it a problem with DB2/UDB API stage?
When I keep format of timestamp as %yyyy-%mm-%dd %hh:%nn:%ss.6
its fetching microseconds but while writing in target which is DB2/UDB API stage ,it is truncating microseconds; even if I fetch microseconds separately and concatenate it .But I can populate it in a sequential file with data type as timestamp.So just wanted to know is it a problem with DB2/UDB API stage?