topic Re: convert the unix timestamp in Talend Studio

convert the unix timestamp

Anonymous — Tue, 05 Jan 2016 17:39:37 GMT

Hello,
I want to convert the unix timestamp 1443696342801 into date ,
i am new to Talend , i converted 1298226724 into date using below logic , please see below attach screen shots:

But i am unable to convert this number 1443696342801
any suggestion will be helpfull
its little urgent
thanks
Amit

Re: convert the unix timestamp

Anonymous — Wed, 06 Jan 2016 07:39:41 GMT

Hi,
Please try to use below expression in tMap:
new java.util.Date(row1.unixtimestamp)
Best regards
Sabrina

Re: convert the unix timestamp

Anonymous — Wed, 06 Jan 2016 10:38:04 GMT

Hi Sabrina,
I have situation regarding Milliseconds to Date Conversion.
There are 2 columns in excell sheet which contains only millisecond values and that have to convert into a Given Date format
Please take following table as Source table..
TRISTARTDATEDA    TRIENDDATEDA
1425148200             1423074600
1423074600       1482604200
Expected Outcome...
TRISTARTDATEDA    TRIENDDATEDA
01-03-2015   05-02-2015
05-02-2015             25-12-2016

Please kindly suggest me solution for this .
I am Waiting for positive reply

Re: convert the unix timestamp

Anonymous — Wed, 06 Jan 2016 11:26:11 GMT

new java.util.Date(row1.unixtimestamp) was the correct answer.
row1.unixtimestamp should be a long type. this will create the correct Date based on the Unix epoch.

Re: convert the unix timestamp

Anonymous — Wed, 06 Jan 2016 12:49:46 GMT

Hey,
Thanx for your reply but it doesnt seems to be working
it giving type casting error
Type Mismatch : Cannot convert from Long to Date
Type Mismatch : Cannot convert from Date to Long
My Source values are coming with Long Type and i put that logic you mentioned above in Target column and i give Date type to it
Is it Right?

Re: convert the unix timestamp

Anonymous — Wed, 06 Jan 2016 12:55:16 GMT

Re: convert the unix timestamp

Anonymous — Wed, 06 Jan 2016 13:48:21 GMT

Hi All,
Thanx for your reply's
Fortunately i have done it by

Re: convert the unix timestamp

Anonymous — Sat, 09 Jan 2016 19:43:15 GMT

Hi,
Please try to use below expression in tMap:
new java.util.Date(row1.unixtimestamp)
Best regards
Sabrina

Hello Sabrina,
Thanks for your prompt reply,
I tried using your logic it seems to be working for ex-1298226724
but when i want to convert unixtimestamp 1443696342801 into date with timestamp i am getting error :invalid error
please check below error
Starting job unix at 22:38 09/01/2016.
connecting to socket on port 3771
connected
Exception in component tOracleOutput_1
java.lang.IllegalArgumentException: Invalid year value
at oracle.sql.TIMESTAMP.getOracleYear(TIMESTAMP.java:786)
at oracle.sql.DATE.toBytes(DATE.java:634)
at oracle.sql.DATE.toBytes(DATE.java:521)
at oracle.sql.DATE.<init>(DATE.java:116)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9325)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8954)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9548)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:249)
at local_project.unix_0_1.unix.tOracleInput_1Process(unix.java:799)
at local_project.unix_0_1.unix.runJobInTOS(unix.java:1236)
at local_project.unix_0_1.unix.main(unix.java:1093)
disconnected
Job unix ended at 22:38 09/01/2016.
for your reference i am attaching the unix epoch converter output
10/1/2015, 1:45:42 PM
i want the output as 10/1/2015, 13:45:42 in 24hrs clock

Re: convert the unix timestamp

Anonymous — Mon, 18 Jan 2016 14:04:32 GMT

Hi Sabrina & All,
Kindly attention Please ....
I have situation in one of ETL job.
i want to convert or remove special charterers from data
Please find Image attached, it has some special charecters and these may found in some of the data ,
so how could we handle this by ETL. There is one column Replace_By which has values that have to replaced by there respective Special Characters. and that particular row which has that Special Character mentioned there have to stored in other file/table/tlogrow.
Please revert if you have any query regarding this.
.

Re: convert the unix timestamp

Anonymous — Tue, 19 Jan 2016 03:16:01 GMT

Hi Shri_Kul1,
Here is a TalendHelpCenter:tReplace which carries out a Search & Replace operation in the input columns defined.
Best regards
Sabrina

Re: convert the unix timestamp

Anonymous — Wed, 20 Jan 2016 08:04:46 GMT

Hi Sabrina
Thans for your suggestion , I have implemented it and its working fine
I am facing another problem now i.e. I want to apply the particular kind of logic on multiple columns present in source I am have already created a job like this
What I want is to repeat the process for multiple columns as follows
can you please guide me regarding this....
waiting for reply

Re: convert the unix timestamp

Anonymous — Fri, 05 Feb 2016 05:41:55 GMT

Hi All,
1 general Question,
What we can do at data warehouse side when we need to delete bunch of records.?
or for which things we should take care about before start delete records from data warehouse?
Except Backup, is one of them.
- What are Best Practice ?
- Operations on DWH before start delete?

Re: convert the unix timestamp

Anonymous — Mon, 15 Feb 2016 12:47:14 GMT

Hi Everybody,
I need help from you,
I am trying to implement an ETL job. I have REMARK column in target table which holds remarks for every column if that particular column failed at validation.
Ex - Suppose that I Have ID column in source (Excel), it should hold only Numeric values but in case if it contains any character then that particular record should go under error table and Remark column hold remark that"ID should be Numeric."
Like wise, i need to implement it for multiple columns & REMARK column should be updated for every column if they all got failed at validation.
like Date Fields- Start_Date Should be less than End_Date.
Description Field - Should contain only 1000 characters max to max.
Any Required (*) field should not be null.
Boolean fields contain only TRUE & FALSE in capital form , should not contain any 'Yes', 'No',123 or nul

Waiting for positive reply..

Re: convert the unix timestamp

Anonymous — Wed, 17 Feb 2016 09:36:54 GMT

Hi Sabrina.. How are you?

Re: convert the unix timestamp

Anonymous — Thu, 18 Feb 2016 06:24:40 GMT

Hi All,

I have done it after all, Please see image attached here and solution for it as follows-
1)Sort out data on the basis of EXPECTED & REJECTED records through your business rule like NAME should not be null if it is null then it should go under ERROR table. After that take 1 variable and put this logic - Source.triNameTX==null?"(triNameTX Should not be Null)":"" and use this variable in REMARK column of ERROR table.
2)START date should less that END date - Var2- (Source.triSTARTDA != null && (Source.triSTARTDA.before(Source.triENDDA))) ||
Source.triSTARTDA == null (Boolean Type)
Var3 -(Source.triSTARTDA!= null && (Source.triSTARTDA.after(Source.triENDDA)))||Source.triSTARTDA == null?"(triSTARTDA Should'nt be NUll or Less than triENDDA)":""
3) Now, Concatenate Var.var1+Var.var2 and run your job & after completion of your job take look into REMARK column , you will see -(triNameTX Should not be Null)(triSTARTDA Should'nt be NUll or Less than triENDDA).
so like wise you can do it for many columns as per your Business Rule.

Rejected Output File..

Re: convert the unix timestamp

SJeshwani1618330394 — Thu, 23 Sep 2021 18:44:09 GMT

Hi,

I resolved to convert epoch time but it's giving me 1985-11-15 01:00:00 instead of 1985-11-15 00:00:00. The only difference is the hours.

It would be great if you can help me 🙂

Regards,

Sheetal

Re: convert the unix timestamp

Anonymous — Fri, 24 Sep 2021 02:34:41 GMT

Hello,

Could you please let us know which value you are working with?

Is it in milliseconds?

You can use below mentioned function directly in tMap

(row1.epochtime == null || row1.epochtime =="") ? null : (new java.util.Date(Long.parseLong(row1.epochtime) * 1000) );

Please give us some Epoch time examples.

Best regards

Sabrina

Re: convert the unix timestamp

SJeshwani1618330394 — Fri, 24 Sep 2021 08:22:38 GMT

Hi Sabrina,

Thank you for your reply.

The epoch value I am working on is: 500860800000

I tried to use "yyyy-MM-dd HH:mm:ss.SSS" but it's still not working.

I used the function you have mentioned, but I am getting an error.

Thanks,

Sheetal

Re: convert the unix timestamp

Anonymous — Sun, 26 Sep 2021 02:49:07 GMT

Hello,

What's error are you getting? Are you still having trouble in "hours" for your case?

Best regards

Sabrina