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

One Star

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

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.
Employee

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

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.
One Star

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

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
Employee

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

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?
One Star

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

Here is the SQL output.
mnp=# select to_char(history_timestamp, 'YYYY-MM-DD HH24:MISmiley FrustratedS.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
Seven Stars

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

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.
One Star

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

I will try & let you know the feedback.
One Star

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

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.
One Star

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

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
One Star

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

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.
One Star

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

Hi Everyone,
Could you please help out on 1 issue regrading milliseconds...
Scenario:- I want only milliseconds or count of milliseconds. suppose i have date in mm/dd/yyyy format
ex - 09/12/2015. so i want milliseconds for this date. 
Milliseconds should not be like 2009-10-30 06:53:43.000553929 or should not be like 2009-10-30 06:53:43.553929;1
It should be like 2578936815....

Waiting for your quick response...

Thanx,
Shri-Kul1
Talend User.