Convert Timestamp

Legacy UserLegacy User Member Posts: 0 Newbie
edited November 2018 in Help
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

Answers

  • TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    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 UserLegacy User Member Posts: 0 Newbie
    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
  • TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    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 UserLegacy User Member Posts: 0 Newbie
    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
  • TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    Hi Chris,

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

    Regards,
    Tobias
  • Legacy UserLegacy User Member Posts: 0 Newbie
    Hi Tobias,

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

    Many Greets

    Chris
  • freemanfreeman Member Posts: 6 Contributor II
    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
  • TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    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 UserLegacy User Member Posts: 0 Newbie
    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:
    image
  • TobiasMalbrechtTobiasMalbrecht Moderator, Employee, Member Posts: 295 RM Product Management
    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
Sign In or Register to comment.