Date Parsing

chavanskc

I have a requirement to trunc time of the date as below. Any event occurring

after 12:00:00 is truncated to 12:00:00

before 12:000 is truncated to 12:00:00

Could not create Computed column matching below requirement

Date Formatted Date

2024-11-21 13:00:00 2024-11-21 12:00:00
2024-11-21 11:00:00 2024-11-21 00:00:00
2024-11-21 19:00:00 2024-11-21 12:00:00
2024-11-21 23:00:00 2024-11-21 12:00:00
2024-11-21 08:00:00 2024-11-21 00:00:00

Operating system used: Windows

chavanskc

Correction

after 12:00:00 is truncated to 12:00:00

before 12:000 is truncated to 00:00:00

tgb417

@chavanskc ,

Welcome to the dataiku community. We are so glad you have joined us.

There are likely many ways to do this in dataiku for example using code recipie with python, r, or SQL. In this case I’m going to talk through a visual recipe approach.

The visual recipe formula function trunc(date d, string unit) does not appear to work to the half day as you are looking to achieve.

I’m not at my instance of dataiku so I can’t build this for you completely. However I think I can describe the approach in a way you can get success. You will have to work out some of the details. This is going to be a moderately advanced set of steps .

First you will need to parse your date time to the officially supported Date time format https://doc.dataiku.com/dss/latest/preparation/dates.html#parsing-dates

In thinking about your questions I was struck with the idea of converting your date time column to Unix time. This produces the number of seconds or milliseconds since Jan 1 1970 which could be rounded.   https://www.unixtimestamp.com/

There are functions built into the visual recipes formula step to convert your dates parsed date times to unix time datePart(date d, string part, [timezone]) https://doc.dataiku.com/dss/latest/formula/index.html#date-functions you want seconds since the epoch (I’d avoid doing the in milliseconds)

With this Unix time stamp value you can use modular division to round the date and time to the nearest half day. Or 43,200 seconds. This also can be done using the formula language function mod(number a, number b) . This would be added to or subtracted from the Unix date. Depending on if you want to round up or down. Here is a stack overflow post on the general topic. https://stackoverflow.com/questions/9639642/how-to-round-unix-timestamp-up-and-down-to-nearest-half-hour

Once you have this rounded Unix date time value. You can convert it back to a date time. This can be done through a visual recipe step https://doc.dataiku.com/dss/latest/preparation/dates.html#converting-from-a-unix-timestamp

This will leave you with a standard format date time string. However, it will be in UTC not your local time zone. Your data as shown does not have a time zone listed so I’m guessing that this is not important to you.