Epoch date conversion ( .json) to Oracle Date

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Epoch date conversion ( .json) to Oracle Date

Post by Vrisha »

I have a input.json file which has a 'CreatedDate' column with value like below
"CreatedDate":"\/Date(1498684373427)\/", -

Job design

Hiearchical stage---> Transformer--> Oracle stage

1) 'CreatedDate' is coming as 'Varchar' while creating the metadata in Schema Library manager

2)I have used the StageVariable to take only 13 digits using Left and Right function and converted it into StringToDecimal

Please let me know if there is any function in Datastage to convert epoch date (Varchar) to date in target( Oracle).
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Welcome!

I don't recall having to work with "epoch" dates in the past and found this site which has a ton of information on the subject including how to do it in Oracle, among other things. So that may be an option for you. Or perhaps custom C++, something UCDI would probably glad to help with. :wink:

There's no specific function for handling epoch dates that I've seen or can find this fine morning. Hopefully someone here has done this before and can provide some guidance.
-craig

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

It must be early, and/or the docs call it UNIX time.

TimestampFromTimet
Returns a time stamp from the given UNIX timet string value.
- Input: timet_string (int32)
- Output: timestamp

https://www.ibm.com/support/knowledgece ... tions.html
Choose a job you love, and you will never have to work a day in your life. - Confucius
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thank you, Guys. I will check the link provided by you and get back to you
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not even close to the same thing. :wink:

That is an ISO timestamp string with a "T" between the date and time. An epoch date is the number of (milli)seconds since 01/01/1970.
-craig

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

yes, the default time in c++ is the 1/1/70 = 0, so it can work with the format. I have not ever needed to convert that to a real date, but I am sure this is a solved problem either in the language or by someone somewhere like boost.org etc. I actually used cpu clock cycles back when I was doing real time code, so I didnt use either (human dates or time since 1970). Then they made variable speed cpus... sigh.

I can look at it with you if you decide this is the best way to deal with it.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

LOL

Thanks. The only example I saw had an input value more like "20090213233130" for that date (which is a little brain-dead) but running your example through that epoch converter site outputs the same external value as in the example, so good. 8)

Just be aware that that is GMT rather than your local time zone, if that matters to you as the date could technically be off by a day, I believe.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks, Guys for your response.

This is what I did below ,but I am facing problem

1) Created a Stage variable(Varchar) to take 10 digits
Left( Right( In_CostConstraintScenarios.CreatedDate,15),10)=svCreatedDate

o/p of Transformer is 1498685212

2) In Transformer stage, I gave TimestampFromTimet(svCreatedDate) ==> datatype in oracle is date......Here I am getting error while compilation Invalid conversion of timestamp to date.

Please let me know what is the mistake I am doing.
Suja
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Solved the problem.

Steps Followed
1) Stage Variable -
svCreatedDate(Varchar)- Left( Right( In_CostConstraintScenarios.CreatedDate,15),10)

2) Created one more Stage variable 'svCD' as Integer with value 'AsInteger(svCreatedDate)'

3)Transformer1 stage
TimestampFromTimet(svCD)-Varchar---> CreatedDate

4) Transformer2 Stage
StringToDate(Out_CostConstraintScenarios.CreatedDate)

5) Loaded as date in Oracle stage
o/p==>6/28/2017 12:00:00 AM
Suja
Post Reply