Defining slots for Timestamps

4 followers
0
Avatar

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?

Priyanka Bagadia Vyas

6 comments

  • Avatar
    Konsta Danyliuk

    Hello Priyanka,
    May I ask you to clarify the request a bit, please?

    • How does your initial dataset look like (perhaps you could attach a screenshot)?
    • What exactly you are trying to achieve (please describe desired result in details) 


    Thank you in advance.

     

    1
  • Avatar
    Will Benica

    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

    1
  • Avatar
    Priyanka Bagadia Vyas

    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.

    0
  • Avatar
    Will Benica

    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

    0
  • Avatar
    Priyanka Bagadia Vyas

    Thank you so much Will! :)

    'Hour' function worked in this case perfectly!

    Thanks a lot!

    0
Please sign in to leave a comment.