Change Date Format

user01936
user01936
in Dataflows

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

 

Comments

  • Valiant
    Valiant 🔵

    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

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

    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) 

    or

    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.

     

    Sincerely,

    Colt

     

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

  • user01936
    user01936

    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,

    Steve 

Categories