Change Date Format

user01936

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

 

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

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