Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Combined date variables creation from separated Year, Month, Date, Hour.
Added on May 11, 2020 12:49PM
Likes: 1
Replies: 7
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
-
Hi @aad34210
,Can you try to parse the date for cont_date_check column before using the sort function?
Answers
-
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 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner
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/4516As 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. -
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 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner
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 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Registered Posts: 25 Partner
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!!
-
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?
