Five Stars ami
Five Stars

convert the unix timestamp

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
14 REPLIES
Moderator

Re: convert the unix timestamp

Hi,
Please try to use below expression in tMap:
new java.util.Date(row1.unixtimestamp)
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: convert the unix timestamp

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
Five Stars

Re: convert the unix timestamp

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

Re: convert the unix timestamp

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

Re: convert the unix timestamp

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?
Kindly find the uploaded image of mytMap....
If i put long type in the target also then job runs but it gives wrong values like
17-01-1970
One Star

Re: convert the unix timestamp

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


Five Stars ami
Five Stars

Re: convert the unix timestamp

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

Re: convert the unix timestamp

Hi Sabrina & All,
Kindly attention Please Smiley Happy....
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.
.  
Moderator

Re: convert the unix timestamp

Hi Shri_Kul1,
Here is a TalendHelpCenter:tReplace which carries out a Search & Replace operation in the input columns defined.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: convert the unix timestamp

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

Re: convert the unix timestamp

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

Re: convert the unix timestamp

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

Re: convert the unix timestamp

Hi Sabrina.. How are you?
One Star

Re: convert the unix timestamp

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