Mysql date API

API stand for Application Programming Interface. API in SQL are used in accessing and processing the data from the database. The different API are used in  Mysql are as follows :

TO_DAYS() - Calculates the day number corresponding to a specific date between year 0 and the specified date.

FROM_DAYS() -Calculates the date corresponding to a day number. This function accepts a day number and returns the corresponding date value.

DATE_ADD() - Adds a specified interval to a date and returns a new date

DATE_SUB() - Subtracts a specified interval from a date and returns a new date

PERIOD_DIFF() -Calculates the difference (in months) between two dates

PERIOD_ADD() - Adds an interval (in months) to a date and returns a new date

Understand with Example

The Tutorial enlighten you on common API used in Mysql  date. In this example, we elaborate  you an example on  each API, that is sufficient to understand them. 

The given below Query  helps you to find out the numbers of days from 0  to the day till now. 

To_Days  ( ) : This is used to calculate the day number corresponding date from year 0 and the specified date.

mysql> select To_days(now())as Days;

Output:-

+--------+
| Days   |
+--------+
| 733761 |
+--------+
1 row in set (0.00 sec)

Query FROM_DAYS():-

The given below Query calculate you the date corresponding to a day number. The given Query  have a function that accept a day number and return you the corresponding date value.

From_Days ( ) : The given below Query is used to calculate the date corresponding to a day number and return you the corresponding date value.

mysql> select From_days(733761)as Date;

Output:-

+------------+
| Date       |
+------------+
| 2008-12-20 |
+------------+
1 row in set (0.00 sec)

Query DATE_ADD() :-

The given below Query adds a specified interval to a current date. In this Query we add a interval of one year to the current date. 

DATE_ADD() : The Query adds a specified interval to a date and returns a new date

mysql> select date_add(curdate(),interval 1 year)as Dateafter1Year;

Output:-

+----------------+
| Dateafter1Year |
+----------------+
| 2009-12-20     |
+----------------+
1 row in set (0.00 sec)

Query DATE_SUB() :-

The below Query is used to subtract a interval of 1 year from the current date.

DATE_SUB() : The DATE_SUB ( ) Subtracts a specified interval from a date and returns a new date

mysql> select date_sub(curdate(),interval 1 year)as DatebeforeYear;

Output:-

+----------------+
| DatebeforeYear |
+----------------+
| 2007-12-20     |
+----------------+
1 row in set (0.00 sec)

Query PERIOD_DIFF():-

The below Syntax is used to show the difference between the two date. The given Query is used to show the period difference between the two consecutive year.

Period_Diff ( ) : The Period_Diff  is used to calculate the period difference between any two dates. 

mysql> select period_diff('2008-12-22','2007-12-22')as Perioddifference;

Output:-

+------------------+
| Perioddifference |
+------------------+
|                1 |
+------------------+
1 row in set, 2 warnings (0.00 sec)

Query PERIOD_ADD():-

The below Query add the interval in month to the specified date given and return you a new days.

PERIOD_ADD() : The Period _ADD adds an interval (in months) to a date and returns a new date

mysql> select period_add(200808,03)as PeriodAdd;

Output:-

+-----------+
| PeriodAdd |
+-----------+
|    200811 |
+-----------+
1 row in set (0.00 sec)