support for oracle timestamp - fractional precision
Currently I can only "use"oracle timestamps in talend to 3 miliseconds.
So, while I can select the timestamp in a sql query, Talend schema only lets me "use" it using the schema format mask "yyyy-MM-dd hh:mm:ss.SSS" I.E. SSS between 1 and 999 is supported as a mask. Is there some other Oracle friendly mask?
Oracle supports up to 9 digits in fractional precision in a timestamp.
So, in practive, my SQL "WHERE... between..." queries (where clause) do not retrieve rows that were timestamped in oracle, as Talend chops off any fractional precision past 3 digits (format mask above) when saving data back to the db.
This is pretty basic stuff.
Can you fix this Talend?
Thanks,
Dave
Hi David,
As soon as the Java type of your Timestamp DbType is correct and you're managing your Query within a SQL query condition; and you connect the resultset in your DB Target; the DatePattern should not be a problem because it's not used.
Your SQLQuery is Oracle oriented and you can use your own SQL DatePattern with all the precision you need; the record you'll extract would be a Date object and that until the load in the DB target.
You should specify a longer DatePattern if you want to store the result in a FlatFile and I'm sure Java can have the precision you're looking for. It's probably just the matter that Talend don't show up the most deeper precision; but I'm sure you can refer to the Java Doc and the java.util.Date or java.sql.Date object to find the appropriate DatePattern.
Hi David, As soon as the Java type of your Timestamp DbType is correct and you're managing your Query within a SQL query condition; and you connect the resultset in your DB Target; the DatePattern should not be a problem because it's not used. Your SQLQuery is Oracle oriented and you can use your own SQL DatePattern with all the precision you need; the record you'll extract would be a Date object and that until the load in the DB target. You should specify a longer DatePattern if you want to store the result in a FlatFile and I'm sure Java can have the precision you're looking for. It's probably just the matter that Talend don't show up the most deeper precision; but I'm sure you can refer to the Java Doc and the java.util.Date or java.sql.Date object to find the appropriate DatePattern.
according to java, the correct format would be yyyy-mm-dd hh:mm:ss.fffffffff
Talend does not represent this correctly, once the date is retrieved in Talend. Try it, you will see.
from java:
valueOf
public static Timestamp valueOf(String s)
Converts a String object in JDBC timestamp escape format to a Timestamp value.
Parameters:
s - timestamp in format yyyy-mm-dd hh:mm:ss.fffffffff
Returns:
corresponding Timestamp value
Throws:
IllegalArgumentException - if the given argument does not have the format yyyy-mm-dd hh:mm:ss.fffffffff
Hi David, As soon as the Java type of your Timestamp DbType is correct and you're managing your Query within a SQL query condition; and you connect the resultset in your DB Target; the DatePattern should not be a problem because it's not used. Your SQLQuery is Oracle oriented and you can use your own SQL DatePattern with all the precision you need; the record you'll extract would be a Date object and that until the load in the DB target. You should specify a longer DatePattern if you want to store the result in a FlatFile and I'm sure Java can have the precision you're looking for. It's probably just the matter that Talend don't show up the most deeper precision; but I'm sure you can refer to the Java Doc and the java.util.Date or java.sql.Date object to find the appropriate DatePattern.
Any update on this. See my new notes I cannot get Talend to support ANY mask other than .SSS at the end of the mask.
