Timestamp with microseconds
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
Timestamp with microseconds
I have a comma delimited flat file as source with two columns
The first column is timestamp in the format 2013-02-27 00:00:00.000
The second column is a varchar
So data looks like
2013-02-27 00:00:00.000,abc
2013-02-28 00:00:00.000,def
and so on..
Note that the timestamp has microseconds specified with only 3 digits rather than 6
I tried using timestamp with microseconds(extended) but it is not reading properly and giving error "delimiter not seen at offset"
i copied my source file into another test file and changed the data as follows
2013-02-27 00:00:00.000000,abc
It was reading properly
So how do I go about with my original data
The first column is timestamp in the format 2013-02-27 00:00:00.000
The second column is a varchar
So data looks like
2013-02-27 00:00:00.000,abc
2013-02-28 00:00:00.000,def
and so on..
Note that the timestamp has microseconds specified with only 3 digits rather than 6
I tried using timestamp with microseconds(extended) but it is not reading properly and giving error "delimiter not seen at offset"
i copied my source file into another test file and changed the data as follows
2013-02-27 00:00:00.000000,abc
It was reading properly
So how do I go about with my original data
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Open the column metadata and specify the detailed format string for that particular timestamp field as "%yyyy-%mm-%dd %hh:%nn:%ss.3".
Set the data type as Timestamp Microsecond 23 3
These settings override the default settings.
Set the data type as Timestamp Microsecond 23 3
These settings override the default settings.
Last edited by ray.wurlod on Thu Jun 26, 2014 4:31 pm, edited 1 time in total.
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.
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
Reopening this discussion due to a new issue
The Sequential File Stage reads data correctly when using the timestamp format suggested by Ray but appends 3 digits to the data
For eg if the source data is 1999-12-31 12:45:36.083
the sequential files reads it as 1999-12-31 12:45:36.083000, also when I had a peek stage the same data i.e., 1999-12-31 12:45:36.083000 was in the director log. Also tried dumping the data to a target sequential file and got the same result
When I read the field as VARCHAR it looks fine
I have tried speciying length and scale as 23 and 3 respectively but to no avail
Any suggestions??
The Sequential File Stage reads data correctly when using the timestamp format suggested by Ray but appends 3 digits to the data
For eg if the source data is 1999-12-31 12:45:36.083
the sequential files reads it as 1999-12-31 12:45:36.083000, also when I had a peek stage the same data i.e., 1999-12-31 12:45:36.083000 was in the director log. Also tried dumping the data to a target sequential file and got the same result
When I read the field as VARCHAR it looks fine
I have tried speciying length and scale as 23 and 3 respectively but to no avail
Any suggestions??
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
Target will be Teradata 14.10
As chulet suggests, the appending of zeroes should not be a problem(hopefully....have to confirm about it from my testing team) but I was wondering whether this is expected behaviour, because since i am explicitly mentioning the timestamp format , it should not append zeroes on its own.
Also as I mentioned before this is happening in view data and peek stage as well
As chulet suggests, the appending of zeroes should not be a problem(hopefully....have to confirm about it from my testing team) but I was wondering whether this is expected behaviour, because since i am explicitly mentioning the timestamp format , it should not append zeroes on its own.
Also as I mentioned before this is happening in view data and peek stage as well
Many items use 'default' formats for displaying the data, that includes View Data and Peek which can confuse people. If you follow Rays advice for the Teradata push it should use the requested format, I would think.
I was hoping for a more detailed target answer - what is the data type of the target field? Assuming we all know but just wanted to get that detail in the thread.
I was hoping for a more detailed target answer - what is the data type of the target field? Assuming we all know but just wanted to get that detail in the thread.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am