Handy date calculations in MySQL

Howdy,

I have been struggeling with date/time calculations for the last couple of years and meanwhile I have quite a collection I would like to share. Note that I have avoided something like date_format(current_date,’%y-%m-01′) because I dont find that very elegant

Simple date calculations

Today

SELECT current_date

Tomorrow

SELECT current_date + interval 1 day

Yesterday (you might guess….)

SELECT current_date - interval 1 day

A week ago

SELECT current_date - interval 1 week

Rather complex date calculations

The first day of last month

SELECT last_day(current_date - interval 2 month) + interval 1 day

The last day of last month

SELECT last_day(current_date - interval 1 month)

The last day of last year

SELECT current_date - INTERVAL DAYOFYEAR(current_date) DAY

the first day of this year

SELECT current_date - INTERVAL DAYOFYEAR(current_date)-1 DAY

last monday

SELECT current_date - INTERVAL weekday(current_date) day

 

If you have more to add, please feel free to put them into the comments and I will happily share them here.

 

Cheers

Andre

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.