date_trunc() in MySQL

Postgres has a great built in function data_trunc() to truncate dates and timestamps by month, day etc. MySQL however is lacking this (among other functions). I saw that many are looking for a solution, as I was. On my online searches I encountered a grate solution by snoys (really, the best I have seen yet), which creates a user defined function that can be used in later in a query.

A Home-Made date_trunc() Function in MySQL

Just run it:

I usually don't need this much resolution (of microsecond) for my needs, so I add a data_format to the query to reduce the resolution exactly to what I was looking for, but you can use any other format.

Let's say I want to see the data by year and months (YYYY-mm):

This is an example of a query where I used date_trunc() to calculate the number of visitors, the number of users that used a specific service, and the conversion rate by months at PreSetGo website:

Hope this helps you to overcome this obstacle. Feel free to comment.

#mysql #monthlyview #conversionsrate