Convert Timestamp

Legacy User

Hi Together,

first I want to thank you for this nice tool rapidminer.
I am new on this, but having a question.

I am reading datas from db2 -> i can see the datas and everythink is okay.

One colum of my datas contains dates/time information in the form:
1090414114957898 (that means 2009 04.14. 11Hour 49Minutes 57seconds 898miliseconds)

Can someone tell my how i can convert the date value 1090414114957898 (type real) in a human readable format?
I tried the preprocessing operator but without a success.
I think the most operators able to handle a date are for converting date to number?

Thanks for ur help

Chris

TobiasMalbrecht

Hi Chris,

as far as I see your real valued timestamp is in fact a string representation of the date and time. Hence, convert the numerical date column to a nominal one. Then apply the [tt]Nominal2Date[/tt] operator with an appropriately set [tt]date_format[/tt] parameter.

Hope that helps,
Tobias

Legacy User

Hi Tobias,

thanks for your reply.
First it was my intention to use the nominal2date operator too.
But I dont know how i can convert my date column to a nominal type.

The only operator that have an nominal format as output is date2nominal.
Or do I have to use a numeric2binominal or numeric2polynominal operator(I tried without success)?

Greets Chris

TobiasMalbrecht

Hi Chris,

yes, you have to use the [tt]Numeric2Polynominal[/tt] operator. Afterwards you can convert the column to a date column by using the [tt]Nominal2Date[/tt] operator.

Kind regards,
Tobias

Legacy User

Hi Tobias,

many many thanks  ;D
You are right i get a nice readable output:
1090414114957898 =    Sat Apr 14 11:49:57 CET 109

I only have a problem with the year (109)
a resolution would be that i use the replace operator an create a regular expression
so i could replace the 1 with 20.
(But i am not so good in building regexpressions)

Maybe u have another idea how i can cut or replace the first 1 in every value?

Greets Chris

TobiasMalbrecht

Hi Chris,

I think it should work when you use [tt]1yyMMdd...[/tt] in the [tt]date_format[/tt] parameter.

Regards,
Tobias

Legacy User

Hi Tobias,

thats it.
Many thanks for your quick and wonderfull help.

Many Greets

Chris

freeman

Hello Tobias,

i have a new Problem with this solution.
The numerical2polynominal changes all my values (inkl. my colum CPU_Usage_%) into nominal type.
After convertin my date (nominal2date) i tried to use the nominla2numerical or nominal2numeric operator.
If i use this, it changes back the date type.

I tried to save the result with the converted date type in an csv and then reload the saved csv-file
--> without success

Is it possible to use the numerical2polynominal just for a special column and not for the complete table?

Greets

Chris

TobiasMalbrecht

Hi Chris,

freeman wrote:
Is it possible to use the numerical2polynominal just for a special column and not for the complete table?

of course it is. Just use an [tt]AttributeSubsetPreprocessing[/tt] and place the [tt]Numerical2Polynominal[/tt] inside the [tt]AttributeSubsetPreprocessing[/tt]. With different filter conditions, you can access specific columns - by value type as well as by name.

Kind regards,
Tobias

Legacy User

Hello Tobias,

thanks for your reply again.
Now i have the table in the rght format:

Servername = nominal
CPU_Usage = Integer
Timestamp = date

But there is one problem left now.
If i want to save the result (as example in a csv-file)
the date type changes into real type.

heres my process:

TobiasMalbrecht

Hi,

freemann wrote:
If i want to save the result (as example in a csv-file) the date type changes into real type.

yes, I think this is due to the internal representation of date values and the momentary incapability of the csv writer to output date values in a correct format. Simply convert the date format back to nominal for outputting.

Regards,
Tobias