Defining slots for Timestamps
Hello,
I want to Convert time stamp value to slots 1-24 depending on the time values (00:00:00 to 00:01:00 to slot 1, 00:01:00 to 00:02:00 to slot 2 etc.). My time stamp is in the format of date followed by time (16-Sep-2016 09:26:33).
I really need to get this sorted. Can someone please help?
-
Hi,
There are a few ways of doing this, depending on what you are trying to do. The easiest would most likely be with the HOUR() function which takes a date and returns the hour.
That would allow you to then group on the hour of the day.
You might also think of using the GROUPBYBIN() function which can take a date and a bin size. This is a group series function and is really useful for click stream analytics.
Cheers,
Will
-
Hi,
My dataset has start time and end time stamps which are in (16-Sep-2016 09:26:33) format. These are the times when a job has been started. Now, what I want to achieve is, based on this tiem stamp, I want to assign slots as 1, 2, 3...24. for each our in a day. for instance 09:26:33 would fall under Slot 10. But the catch is that my date has AM and PM in the format. So i first need to change the timestamp to a 24 hour and then assign the slots. am not worried about the actual data which is 16 Sep in above example but I am worried about the timestamp with the date. Could you please help me.
-
I'd try to use the ASDATE function first. You can then build up groups if desired for various things.
GROUPBY(HOUR(ASDATE(<string>,<parsePattern>)))
This will reduce the dates to just the hour.
It looks like your dates - e.g.16-Sep-2016 09:26:33 would need to use a parse pattern like this: 'dd-MMM-yyyy hh:mm:ss' don't forget to quote it.
Cheers,
Will
-
Have you tried using the HOUR() function to cast the DateTime into an integer for the hour? (Edit: Saw you'd answered it before I replied.)
Please sign in to leave a comment.
Comments
6 comments