topic Re: support for oracle timestamp - fractional precision in Talend Studio

support for oracle timestamp - fractional precision

Anonymous — Sat, 16 Nov 2024 13:06:19 GMT

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

Re: support for oracle timestamp - fractional precision

Anonymous — Tue, 08 Feb 2011 01:04:43 GMT

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.

Re: support for oracle timestamp - fractional precision

Anonymous — Tue, 08 Feb 2011 23:06:33 GMT

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

Re: support for oracle timestamp - fractional precision

Anonymous — Wed, 16 Feb 2011 16:18:28 GMT

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.