The Dataiku Frontrunner Awards are now accepting submissions until July 15 to recognize your achievements! ENTER YOUR SUBMISSION

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

Solved!
aad34210
Level 3
Level 3
Combined date variables creation from separated Year, Month, Date, Hour.

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?

 

1 Solution
KimmyC
Dataiker
Dataiker

Hi @aad34210 ,

Can you try to parse the date for cont_date_check column before using the sort function?

View solution in original post

6 Replies
KimmyC
Dataiker
Dataiker

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
Level 3
Level 3
Author

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-am... 

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
Dataiker
Dataiker

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
Level 3
Level 3
Author

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!

KimmyC
Dataiker
Dataiker

Hi @aad34210 ,

Can you try to parse the date for cont_date_check column before using the sort function?

View solution in original post

aad34210
Level 3
Level 3
Author

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!!

A banner prompting to get Dataiku DSS
Public