How to load data with date containing microseconds from file to DB

[Anonymous]

How to load file with field having date format having microseconds "yyyy-MM-dd HH:mm:ss.SSSSSS". The format is displayed in t_Map supports upto milliseconds.

[Anonymous]

I just tested this in Talend 3.2.1 and it worked fine for me. Just update the date pattern in the tMap editor to "yyyy-MM-dd HH:mm:ss.SSSSSS" for the relevant column.

[Anonymous]

Talend Version 3.2.M3_r28760.
This is the file i am trying to load into DB. I tranform the timestamp value in the file using the format "yyyy-MM-dd HH:mm:ss.SSSSSS". If you notice the value after transformation, it is truncating the digits in the microsecond part.
2009-10-30 06:53:43.553929 ==> 2009-10-30 07:02:56.929 (missing the milliseconds)
File: E:/projects/Talend/ph demo/test_input.lst
history_timestamp;pid
2009-10-30 06:53:43.553929;1
2009-10-20 16:33:14.386345;2
2009-10-20 16:48:16.638456;3
2009-10-20 16:48:21.704456;4
2009-10-20 16:50:45.948456;5

mnp=# select * from scratch.table1;
history_timestamp | pid
-------------------------+-----
2009-10-30 07:02:56.929 | 1
2009-10-20 16:39:40.345 | 2
2009-10-20 16:58:54.456 | 3
2009-10-20 17:00:05.456 | 4
2009-10-20 17:06:33.456 | 5
(5 rows)
Any help on this will be appreciated.
Thanks
Srinivas

[Anonymous]

Hi Scrinivas, I haven't tried this myself, but is there a way with your select to statement print the timestamp in a specific format something like select formatdate(history_timestamp, "yyyy-MM-dd HH:mm:ss.SSSSSS")?
I just noticed in the example above that none of the timestamps match the input file. Each is off. Is that expected?

[Anonymous]

Here is the SQL output.
mnp=# select to_char(history_timestamp, 'YYYY-MM-DD HH24:MI S.US'), pid from scratch.table1; -- US Postgres format string for microseconds
to_char | pid
----------------------------+-----
2009-10-30 07:02:56.929000 | 1
2009-10-20 16:39:40.345000 | 2
2009-10-20 16:58:54.456000 | 3
2009-10-20 17:00:05.456000 | 4
2009-10-20 17:06:33.456000 | 5
(5 rows).

You are right the microseconds from the file doesn't match data in the table. That is the issue i am trying to resolve.
Srinivas

[rbaldwin]

You have to use a java.sql.Timestamp object to get time resolution greater than Millisecond. For example, the following code:

java.sql.Timestamp t = new java.sql.Timestamp(TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",ts).getTime());
t.setNanos(Integer.parseInt(ts.substring(ts.lastIndexOf(".") + 1)));
System.out.println(t);

Prints out:

2009-10-30 06:53:43.000553929

Hopefully that provides some resolution to your issue.

[Anonymous]

I will try & let you know the feedback.

[_AnonymousUser]

OK to use the java timestamp in tmap, but how to use it in tPostgresqlInput ? In the schema windows, I can't choose timestamp in java type list.

[Anonymous]

We are performing something similar where we bring in the timestamp from a db2 system (up to the microsecond precision) and writing it to a Teradata database using timestamp(6). The Date (java.util.Date) only gives you millisecond precision so the last 3 decimal digits are replaced with zeros. We got around this by using the "Object" Java type and "Timestamp" DB types in the schema definitions in the input, tmap and output components and it worked flawlessly. I assume the Java Type stored is probably a java.sql.Timestamp so if you need to manipulate in the tmap, you will most likely need to Java cast it. In our case, we didn't have to. We are currently using TOS 5.0.2. Hopefully the tPostgresqlOutput component behaves the same way.
Noel

[Anonymous]

how would I go about using java timestamp in tMap?
my input is a string in the format "yyyy-MM-dd-HH.mm.ss.SSSSSS"
and I am trying to convert it to a date with a precision to microseconds.