DevChunks

Time and Date Functions in MySQL

Working with dates and times in MySQL is straightforward and there are several MySQL functions to do the calculations for you. Some of the most common requirements are to calculate the current date and time, and this can be done with the CURRENT_DATE() and NOW() functions like this:

{code type=php}
mysql> SELECT CURRENT_DATE();
2011-03-01
mysql> SELECT NOW();
2011-03-01 17:30:00
{/code}

Sometimes you might want to extract some of the date or time information from a value held in a field. The year(), month(), hour(), minute() and second() functions will extract the pertinent part of the date value as the following examples demonstrate:

{code type=php}
mysql> SELECT YEAR(‘2011-03-01’);
2011
mysql> SELECT MONTH(‘2011-03-01’);
03
mysql> SELECT HOUR(NOW());
17
{/code}

You can use four different functions to extract day information from the date depending on how you need the result. This example shows these functions:

{code type=php}
mysql> SELECT DAYOFMONTH(NOW());
2
mysql> SELECT DAYNAME(NOW());
Tuesday
mysql> SELECT DAYOFWEEK(NOW());
3
mysql> SELECT DAYOFYEAR(NOW());
61
{/code}

Now that you know how to extract date information, you can use that in calculations. To work out the age of a person, you can subtract their birthday from the current date.

{code type=php}
SELECT YEAR(CURRENT_DATE()) – YEAR(birthday);
{/code}

This will work as long as the person has already celebrated their birthday. If the birthday is later in the year then it will return an incorrect value. The only way around this is to test on the month and day portion of the birthday. This code segment uses the date_format() function to calculate the age if the birthday has passed this year:

{code type=php}
SELECT YEAR(CURRENT_DATE()) – YEAR(birthday) –
(date_format(CURRENT_DATE(), ‘%m-%y’)<) AS age;
{/code}

Share:

One Response

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the