topic Re: How to load data with date containing microseconds from file to DB in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 13:42:56 GMT

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.

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

Anonymous — Fri, 30 Oct 2009 01:19:18 GMT

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.

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

Anonymous — Fri, 30 Oct 2009 19:00:23 GMT

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

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

Anonymous — Tue, 10 Nov 2009 19:01:58 GMT

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?

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

Anonymous — Tue, 10 Nov 2009 20:45:09 GMT

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

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

rbaldwin — Wed, 11 Nov 2009 19:09:42 GMT

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.

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

Anonymous — Fri, 13 Nov 2009 20:59:17 GMT

I will try & let you know the feedback.

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

_AnonymousUser — Tue, 22 Dec 2009 16:49:00 GMT

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.

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

Anonymous — Fri, 01 Nov 2013 15:32:40 GMT

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

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

Anonymous — Thu, 22 May 2014 22:08:58 GMT

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.

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

Anonymous — Fri, 09 Oct 2015 10:50:09 GMT

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.