Suppose I have a DateTime variable that I want to cluster on. I only really care about the day, hour, and minute. Is it wrong to create a column in my data set for each of these or should I keep the one DateTime variable and convert it to double with the following formula:

continousDateTime = day + hour/24 + minute/1440

If I do it this way, I feel like I will be losing out on the minute granularity that I need to conserve. Let me know your thoughts and experiences :)


First, your conversion formula does not make sense. You should convert time to minutes after some specific time point (using, e.g., your first timestamp or 1970-01-01 00:00:00 as an "anchor time"). The formula will look something like this:

delta_minutes = day*24*60 + hour*60 + minute

Much software will do this automatically. For instance, in R, POSIXct internally stores timestamps as seconds since the beginning of 1970 UTC.

Once you have converted your timestamps like this, you can cluster away.

If you keep your day, hour and minute in three separate columns, you will cluster in three dimensions. Then timestamps seven hours apart on one day (e.g., 2016-06-30 10:00 and 2016-06.30 17:00) may be sorted into different clusters, whereas timestamps with the same hour and minute three days apart (e.g., 2016-06-30 10:00 and 2016-07-02 10:00) may be sorted in the same cluster. This is presumably not what you want.

  • $\begingroup$ Ah, I see thank you for your input. :) I don't understand why my formula is incorrect though... Your formula has units of minutes and mine has units of days. $\endgroup$ – user2253546 Jun 30 '16 at 20:29
  • $\begingroup$ Ah, you are right. Sorry. I misunderstood. My preconceptions got in the way. $\endgroup$ – Stephan Kolassa Jul 1 '16 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.