Combined date variables creation from separated Year, Month, Date, Hour.

Options
aad34210
aad34210 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner

Hello.

I'm trying to create new combined date variable from separated Year, Month, Date and Hour variables as "YYYY/MM/DD HH:MI:SS" format.

However, I can't create it using processor step like below screenshot.

Could you please someone help me how to create new datetime variable as "YYYY/MM/DD HH:MI:SS" format?

Best Answer

Answers

  • KimmyC
    KimmyC Dataiker Posts: 34 Dataiker
    Options

    Hi,

    You can put the "00:00" in a string format, the formula would look like this concat(year,"/",month,"/",day," ",hour,":00:00").

    Once you have that new column, click on the column > Parse date and this will create a new column of Date type.

  • aad34210
    aad34210 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner
    Options

    Hi, @KimmyC

    Thank you for your prompt answer.

    I tried to implement guided formula but unfortunately it did not works not fine.
    Because month, date and hours variables values does not double digit format. (e.g. 01 -> 1)

    So I applied change each column's value digit format if under 9, also added Dataiku default date and time format refer to below link.
    https://community.dataiku.com/t5/Product-Knowledge-Base/How-Dataiku-DSS-Handles-and-Displays-Date-amp-Time/ta-p/4516

    As the result, below formula worked fine.

    concat(year,"-",if(month<=9,"0"+month,month),"-",if(day<=9,"0"+day,day),"T",if(hour<=9,"0"+hour,hour),":00:00.000Z")

    Result -> 2018-11-30T12:00:00.000Z , 2018-01-30T01:00:00.000Z ...

    However, this formula little bit messy so please let me know more smarter way if possible.

    Best regards.
    Thank you.

  • KimmyC
    KimmyC Dataiker Posts: 34 Dataiker
    Options

    Hi @aad34210
    ,

    The month, date and hour variables should not need to be double digit format. As long as the date is a valid one, DSS should be able to parse it to a Date format. Additionally, you can specify the specific date format in the parse step. By default, it's always yyyy/MM/dd HH:mm:ss.

    Can you please try parsing the date without formatting the variables to double digit, i.e. copy my formula above and then try parsing the date?

    Thanks!

  • aad34210
    aad34210 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner
    Options

    Hi, @KimmyC

    Thank you for your prompt reply.

    I tried to implement your guide and it seems looks fine. (No red colors in created column)
    But sort order function in this column can't works correctly.

    I took screenshot which already sorted using guided formula.

    [Explanation of columns in attached screenshot]
    - year / month / day / hour : Source columns
    - cont_date_check : Created from guided formula
    - cont_date: Created from my formula (Yesterday I replied)

    You can see cont_date_check column's sort order didn't work correctly.
    For example, the value 2017/12/30 6:00:00 should sorted at 2nd row in 2017/12/30, but this value sorted last row in 2017/12/30 ...

    Could you please tell me how to modify for correct it ?

    If you have any questions please let me know.

    Best regards.
    Thank you!

  • aad34210
    aad34210 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner
    Options

    Hi, @KimmyC

    Thank you for your prompt reply.
    I successfully done which I wanted to. Date time format and sort order works fine.

    Thank you for your answers!!

  • ash_m
    ash_m Registered Posts: 2 ✭✭
    Options

    How to refresh a dataset using time partitioning special keywords. I want to refresh for current month and previous month . Can i use both special keywords in scenario?

Setup Info
    Tags
      Help me…