Playing with Date and Time in MySQL

Manipulating date and time in database is an important task in any database driven application. MySQL is the most popular open source database deployed widely. Here are some tasks that you can perform on date and time using MySQL built in functions.

Adding interval to a date

DATE_ADD function allows adding/subtracting(using - sign) any interval to date/datetime. The usage of this function is as follows

DATE_ADD(date,INTERVAL [expression] [unit])

unit can be one of the following

MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_MICROSECOND,MINUTE_MICROSECOND,MINUTE_SECOND,HOUR_MICROSECOND,HOUR_SECOND,HOUR_MINUTE,DAY_MICROSECOND,DAY_SECOND,DAY_MINUTE,DAY_HOUR,YEAR_MONTH

expression depends upon the unit. We will explore some units and respected expressions in this post.

Note: All example queries use column name "column_dt" for a column of type datetime.

Adding SECOND(s) to DATETIME
Following query will add 1 second to the value of column_dt

SELECT DATE_ADD(column_dt INTERVAL 1 SECOND)

Adding HOURS:MINUTES:SECONDS and MICROSECONDS to DATETIME
Following query will add 12 hours 40 minutes and 30 seconds to column_dt

SELECT DATE_ADD(column_dt INTERVAL '12:40:30' HOUR_SECOND)

To include microseconds we will use HOUR_MICROSECOND unit like this

SELECT DATE_ADD(column_dt INTERVAL '12:40:30.500' HOUR_MICROSECOND)

Adding DAYS and DAYS+HOURS:MINUTES:SECONDS
Following query will add 2 days to the value of column_dt

SELECT DATE_ADD(column_dt INTERVAL 1 DAY)

To add days along with hours,minutes and seconds we use DAY_SECOND,DAY_MINUTE or DAY_HOUR unit.
Following query will add 2 days , 10 hours 12 minutes and 10 seconds

SELECT DATE_ADD(column_dt INTERVAL '2 10:12:10' DAY_SECOND)

Following query will add 3 days 10 hours and 12 minutes

SELECT DATE_ADD(column_dt INTERVAL '3 10:12' DAY_MINUTE)

Following query will add 3 days and 2 hours

SELECT DATE_ADD(column_dt INTERVAL '3 10' DAY_HOUR)

For complete reference of units and expressions for DATE_ADD please refer to this link.

Extracting parts from DATETIME

DATE function extracts only date part from DATETIME type

SELECT DATE('2014-01-01 12:33:40') will return '2014-01-01'

To extract other parts from datetime like hour, minute , second etc there is a function EXTRACT. It syntax is as follows

EXTRACT(unit FROM datetime)

Unit in EXTRACT are same as unit in DATE_ADD

• SELECT EXTRACT (YEAR_MONTH FROM '2014-01-11') will return 201401
• SELECT EXTRACT(DAY_MINUTE FROM '2014-01-11 01:02:03') will return 20102 
• SELECT EXTRACT(YEAR FROM '2014-01-11') will return 2014

Getting variants of DAY from DATETIME

SELECT DAYNAME('2014-05-15') returns Thursday 

SELECT DAYOFMONTH('2014-05-15') returns 15 

SELECT DAYOFWEEK('2014-05-15') returns 5 (1=Sunday,2=Monday,3=Tuesday...) 

SELECT DAYOFYEAR('2007-02-03') returns 34 (DAYOFYEAR returns day in the year values between 1 - 366)

Converting DATETIME between two TIMEZONES 

CONVERT_TZ(dt,from_tz,to_tz) function converts the passed dt parameter into to_tz timezone from from_tz . Timezones can be passed in two forms

• Name of timezone like GMT,MET or Europe/Helsinki
  View here details of Timezones in MySQL
• Value as offset from UTC like +00:00 , +10:00

CONVERT_TZ('2014-05-15 12:00:00','GMT','MET') will return
'2014-05-15 13:00:00'
CONVERT_TZ('2014-05-15 05:00:00','+00:00','+05:00') will return
'2014-05-15 10:00:00'