Change Date Format

I currently have a date column with data represented in the following format: 2017-10-23 08:56:21


For a full picture, this represents the time of day I received a phone call.


I would like to change this so that I can count the number of calls per day and per hour of operation on a chart.  For example, for the past month, what is call frequency on ALL Monday's, Tuesday, etc.


Then, drill down through a specific day to determine when most of the calls come in as it pertains to time of day.


Any help would be appreciated. 


Thank you,

Steve Gonser



  • Steve,


    What I would do is first break the datetime into days using:

    DATE_SUB(DATE_FORMAT(`DateColumn`, '%Y-%m-%d'), INTERVAL 0 DAY) 

    (I use the date_sub to subtract 0 days as sometimes beastmode doesn't recognize date_format as a date.


    Using that beastmode as your x axis, should let you count same column on your series to get a 'Call each day'


    If you'd rather have it on all Monday's, Tuesday's etc.. then use


    and just limit your time via the date filter.


    For the hours breakdown, use:

    DATE_SUB(DATE_FORMAT(`DateColumn`, '%Y-%m-%d %H'), INTERVAL 0 DAY) 


    DATE_FORMAT(`DateColumn`, '%H')

    to only return the hours.


    There's several different ways you could format the data, just let me know if you have any questions on the above.





    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • Colt, 


    Thank you.


    I used 'DAYOFWEEK(DATE_SUB(DATE_FORMAT(`DateColumn`, '%Y-%m-%d'), INTERVAL 0 DAY))'



    It return days as "1 - 7" - How would I format this for "Sunday - Saturday"


    Thank you,