Extract time as an Integer from a Timestamp

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
Lapsusone
Participant
Posts: 4
Joined: Thu Jun 15, 2017 10:10 am

Extract time as an Integer from a Timestamp

Post by Lapsusone »

Hello,

I'm trying to extract the time from a Timestamp column as an integer.

For example, for a timestamp like "2017-06-15 12:30:55", I want to output an integer equal to 1230.

I have mainly 2 issues I need to understand:

First, I tried using the following function:

Code: Select all

TimestampToDecimal(DSLink2.TIMESTAMP, "%hh%nn")
Instead of the correct result as a decimal, I get the following error :
APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%hh%nn" for conversion "decimal=decimal_from_timestamp[%yyyy%mm%dd%hh%nn%ss](timestamp)": APT_Conversion_Decimal_TimeStamp: Invalid Format [%hh%nn] used for string_from_date type conversion.
This to me seem strange as the following function gets me the correct result as a decimal:
TimeToDecimal(TimestampToTime(DSLink2.TIMESTAMP), "%hh%nn")
Second issue is converting that from a Decimal to an Integer. I just call AsInteger on the above function, but instead I get a warning and value is set to 0.
APT_CombinedOperatorController,0: Conversion error calling conversion routine decimal_from_time data may have been lost
Can someone help me understand what's going on?
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

Lapsusone,

It can be acheived by

substring

Stage variable=DSLink2.TIMESTAMP[12,8], --- o/p:12:30:55
after you get the time part, use HoursFromTime and MinutesFromTime,


concatenate these functions to get the desired results HoursFromTime:MinutesFromTime and store it in integer.
anudpETL
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So what actually is your "workaround"?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Lapsusone
Participant
Posts: 4
Joined: Thu Jun 15, 2017 10:10 am

Post by Lapsusone »

As anudeepdasari001 mentioned, I used substring and it works this way. I still don't know why the functions I mentioned above don't work.

Code: Select all

EREPLACE(DSLink2.TIMESTAMP[12,8],":","")[1,4]
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So, out of curiosity, is your "timestamp" field actually a string?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Lapsusone
Participant
Posts: 4
Joined: Thu Jun 15, 2017 10:10 am

Post by Lapsusone »

No, it's still a Timestamp column. I guess under the hood DataStage treats Timestamp fields as a string.
anudeepdasari001
Participant
Posts: 58
Joined: Mon Dec 14, 2015 3:16 pm
Location: Arizona

Post by anudeepdasari001 »

By the way from which source are you getting this timestamp field.
Could you brief us the design of the job.
anudpETL
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

pretty sure timestamps are numeric, but can be converted to strings on the fly due to the common need to do that.
Lapsusone
Participant
Posts: 4
Joined: Thu Jun 15, 2017 10:10 am

Post by Lapsusone »

To keep it simple, the job reads from a sequential file a timestamp in the default format and loads the date and time as separate fields in an Oracle database.
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

Is it in military time? Like 1 is 13:00 and midnight is 00:00 etc?

If so, a simple substring and e-replace ought to pull what you need from the string, and then convert that string back to integer.
Post Reply