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

[aad34210]

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?

[KimmyC]

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]

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]

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]

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 · Accepted solution]

Hi @aad34210 ,

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

[aad34210]

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]

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?