Dates functions with MySQL and unix timestamp

MySQL has powerful functions built in to manipulate data with dates. These functions only work within MySQL providing your using their own date/time stamp. If your like me, your much rather use the PHP function time() to get the Unix timestamp and store that in the database. It seems a shame to miss out on these features because we use Unix time. Never fear, the following examples shows how to get round this using FROM_UNIXTIME(). So what happens now if we want to SELECT or GROUP data by periods, such as by week, month or year. Take one look in the MySQL manual and there are few practical examples on how this can be done using only the Unix timestamp. Below I run through a practical example and how this can easily be achieved. If you want a mock up table to try it out, run the following SQL. This table is a vastly simplified version of a e-commerce system order table, but the logic here could be applied to any type of database driven web site.

CREATE TABLE `orders` (
`order_ID` int(11) NOT NULL auto_increment,
`timeordered` int(11) NOT NULL default '0',
`grandtotal` double(10,2) NOT NULL default '0.00'
PRIMARY KEY  (`order_ID`)) ;

This table contains the Unix timestamp in `timeordered` and `grandtotal` contains a decimal number. We are going to construct a SQL query that will list the number of orders in a month, the total amount of all those orders and the average value of those orders. Lines 1, 2, 4 are standard functions used to calculate our values. The interesting lines are 5 and 6. If I was using the normal MySQL timstamp and could simply write GROUP BY MONTH(), YEAR() to achieve the results we want. You may ask why are we using both MONTH() and YEAR() when we just need it to group by MONTH(). Well the answer is you could just use MONTH(), but that would only ever return 12 rows (1 for each month) no matter over how many years. So for example it would group January 2004, January 2005 or any other January. For some systems this would be undesirable. Using YEAR() ensure only one month and one year is in one return row.

SELECT COUNT(order_ID) as `number`,
SUM(grandtotal) as `cgrandtotal`,
FROM_UNIXTIME(timeordered, '%M %Y') as `fdate`,
AVG(grandtotal) AS `averagegrand`
FROM `orders`
GROUP BY MONTH(FROM_UNIXTIME(timeordered,'%Y-%m-%d %H.%i.%s')),
YEAR(FROM_UNIXTIME(timeordered,'%Y-%m-%d %H.%i.%s'))
ORDER BY timeordered DESC

Normally we put the column name in the brackets of the function of the MySQL date (ie YEAR(column-name)). But because we are using Unix time we must use the functionFROM_UNIXTIME(column-name-of-unixtime, format). The format parameter simply specifies how to turn the Unix time to a understandable timestamp for MySQL, the ones shown on lines 7 and 8 seem to work fine, I believe there may be shorter ones that can be used.

So the query above will return something like this (after adding your own dummy data!):

NUMBER	CGRANDTOTAL	FDATE	AVERAGEGRAND
8	532.00	January 2008	66.50
21	1201.20	December 2007	57.20
17	1041.25	November 2007	61.25

The example below assumes you have some basic knowledge of SQL and has been tested on MySQL 4.1.

MySQL DATEDIFF() vs TIMEDIFF(): What’s the Difference?

Two date functions included in MySQL are DATEDIFF() and TIMEDIFF().

Both functions do a similar thing, but with some meaningful differences.

The following table summarizes the difference between these two functions:

DATEDIFF()	TIMEDIFF()
Result is expressed as a value in days.	Result is expressed as a time value.
Compares only the date value of its arguments.	Compares the time value of its arguments.
Accepts date or date-and-time expressions.	Accepts time or date-and-time expressions.
Both arguments can be of a different type (date or date-and-time).	Both arguments must be the same type (either time or date-and-time).

Example 1 – Basic Difference

Here’s an example that demonstrates the basic difference between these functions.

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF';

Result:

+----------+----------+
| DATEDIFF | TIMEDIFF |
+----------+----------+
|        1 | 24:00:00 |
+----------+----------+

So we can see that DATEDIFF() returned 1, meaning “1 day”, and TIMEDIFF() returned 24:00:00 which is the time representation of exactly 1 day.

Example 2 – Specifying a Time Value

Let’s see what happens if we increase the time value of one of the variables.

SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF';

Result:

+----------+----------+
| DATEDIFF | TIMEDIFF |
+----------+----------+
|        1 | 36:15:35 |
+----------+----------+

So DATEDIFF() returns the same result as in the previous example. This is because it only compares the date values (it ignores any time values).

The TIMEDIFF() function, on the other hand, compares the time, and therefore it returns a more precise result. It shows us that there are 36 hours, 15 minutes, and 35 seconds between the two date-and-time values.

Example 3 – Wrong Argument Types

Here’s an example of what happens when you pass the wrong argument types to each function.

SET @date1 = '2010-10-11', @date2 = '2010-10-10', @time1 = '12:15:35', @time2 = '00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF Date',
  DATEDIFF(@time1, @time2) AS 'DATEDIFF Time',
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF Date',
  TIMEDIFF(@time1, @time2) AS 'TIMEDIFF Time';

Result:

+---------------+---------------+---------------+---------------+
| DATEDIFF Date | DATEDIFF Time | TIMEDIFF Date | TIMEDIFF Time |
+---------------+---------------+---------------+---------------+
|             1 |          NULL | 00:00:00      | 12:15:35      |
+---------------+---------------+---------------+---------------+

The first and last results are fine, because the correct argument types were passed in. However, the middle two results had the wrong data type passed in and therefore the correct result couldn’t be calculated.

Example 4 – Mixed Argument Types

Here’s what happens if you provide two different data types to each function.

SET @thedate = '2010-10-11', @thetime = '12:15:35', @thedatetime = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@thedate, @thedatetime) AS 'DATEDIFF',
  TIMEDIFF(@thetime, @thedatetime) AS 'TIMEDIFF';

Result:

+----------+----------+
| DATEDIFF | TIMEDIFF |
+----------+----------+
|        1 | NULL     |
+----------+----------+

So we can see that DATEDIFF() handles mixed data types fine (as long as they’re either date or date-and-time).

However, TIMEDIFF() requires that both arguments are of the same type, so we get NULL, even though both arguments are of a type that the function supports (time and date-and-time).

We can confirm that both types are in fact supported by this function with the following example:

SET @thetime1 = '12:15:35', @thetime2 = '10:15:35', @thedatetime1 = '2010-10-12 00:00:00', @thedatetime2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@thetime1, @thetime2) AS 'time',
  TIMEDIFF(@thedatetime1, @thedatetime2) AS 'datetime';

Result:

+----------+----------+
| time     | datetime |
+----------+----------+
| 02:00:00 | 48:00:00 |
+----------+----------+

MySQL DATEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?

This article looks at the difference between two MySQL functions; DATEDIFF() and TIMESTAMPDIFF().

Both functions return the difference between two dates and/or times, but the result is different between the two functions.

The following table summarizes the difference between these two functions:

DATEDIFF()	TIMESTAMPDIFF()
Requires 2 arguments.	Requires 3 arguments.
Subtracts the 2nd argument from the 1st (expr1 − expr2).	Subtracts the 2nd argument from the 3rd (expr2 − expr1).
Result is expressed as a value in days.	Result is expressed as the unit provided by the first argument.
Can compare only the date value of its arguments.	Can compare the date and time value of its arguments.

Example 1 – Basic Operation

Here’s an example that demonstrates how these functions work, and how the results are different, even when using the same unit.

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |            -1 |
+----------+---------------+

So both functions return the difference in days, however one result is positive and the other negative. This is because DATEDIFF() subtracts the second date from the first, whereas TIMESTAMPDIFF() subtracts the first date from the second.

Example 2 – Changing the Unit

As the previous example demonstrates, the TIMESTAMPDIFF() allows you to specify a unit for the results to be returned as (in fact, it requires you to specify the unit). On the other hand, DATEDIFF() doesn’t allow you to specify a unit. It only returns the result in days.

So we could modify the previous example so that TIMESTAMPDIFF() returns the number of hours instead of days:

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |           -24 |
+----------+---------------+

You can go all the way to microseconds:

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |  -86400000000 |
+----------+---------------+

Example 3 – Precision

The precision of DATEDIFF() is one day, and TIMESTAMPDIFF() can go down to the microsecond. However the precision of TIMESTAMPDIFF() (and the unit that it compares) still depends on the specified unit.

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-10 23:59:59';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'Microseconds';

Result:

+----------+------+-------+---------+---------+--------------+
| DATEDIFF | Days | Hours | Minutes | Seconds | Microseconds |
+----------+------+-------+---------+---------+--------------+
|        0 |    0 |    23 |    1439 |   86399 |  86399000000 |
+----------+------+-------+---------+---------+--------------+

And here’s the result if we increment the 2nd date by one second (which brings it to the next day):

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-11 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'Microseconds';

Result:

+----------+------+-------+---------+---------+--------------+
| DATEDIFF | Days | Hours | Minutes | Seconds | Microseconds |
+----------+------+-------+---------+---------+--------------+
|       -1 |    1 |    24 |    1440 |   86400 |  86400000000 |
+----------+------+-------+---------+---------+--------------+

Here’s another example, this time seeing how it looks when we return months, quarters, and years when the difference is one month (or 31 days):

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-11-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(month, @date1, @date2) AS 'Month',
  TIMESTAMPDIFF(quarter, @date1, @date2) AS 'Quarter',
  TIMESTAMPDIFF(year, @date1, @date2) AS 'Year';

Result:

+----------+------+-------+---------+------+
| DATEDIFF | Days | Month | Quarter | Year |
+----------+------+-------+---------+------+
|      -31 |   31 |     1 |       0 |    0 |
+----------+------+-------+---------+------+

Example 4 – Wrong Argument Types

Both functions return null if they are passed the wrong argument type.

SET @time1 = '12:15:35', @time2 = '00:00:00';
SELECT 
  DATEDIFF(@time1, @time2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @time1, @time2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|     NULL |          NULL |
+----------+---------------+

Example 5 – Mixed Argument Types

Both functions allow you to provide a date as one argument and a datetime as another argument.

SET @thedate = '2010-10-11', @thedatetime = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@thedate, @thedatetime) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @thedate, @thedatetime) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |            -1 |
+----------+---------------+

DATEDIFF() Examples – MySQL

In MySQL, you can use the DATEDIFF() function to find the difference between two dates. The way it works is, you provide two arguments (one for each date), and DATEDIFF() will return the number of days between the two dates.

Examples below.

Syntax

First, here’s the syntax:

DATEDIFF(expr1,expr2)

Where expr1 is the first date, and expr2 is the second date.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT DATEDIFF('2020-10-30', '2020-10-01') AS 'Result';

Result:

+--------+
| Result |
+--------+
|     29 |
+--------+

In this example, the first date is later than the second date. In this case we get a positive return value.

Example 2 – Comparison with an Earlier Date

The first date doesn’t have to be a later date than the second date. You can use an earlier date for the first argument and it will return a negative value. If we swap those two arguments around, we get the following:

SELECT DATEDIFF('2020-10-01', '2020-10-30') AS 'Result';

Result:

+--------+
| Result |
+--------+
|    -29 |
+--------+

Example 3 – Datetime Values

When used with datetime values, only the date part is used to compare the dates. Example:

SELECT 
  DATEDIFF('2020-10-30 23:59:59', '2020-10-01') AS 'Result 1',
  DATEDIFF('2020-10-01 23:59:59', '2020-10-30') AS 'Result 2';

Result:

+----------+----------+
| Result 1 | Result 2 |
+----------+----------+
|       29 |      -29 |
+----------+----------+

Example 4 – Database Query

Here’s an example of using DATEDIFF() in a database query. In this example, I compare the payment_date column with today’s date (by using the CURDATE() function to return today’s date):

USE sakila;
SELECT
  DATE(payment_date) AS 'Date/Time',
  CURDATE(),
  DATEDIFF(payment_date, CURDATE()) AS 'Date'
FROM payment
WHERE payment_id = 1;

Result:

+------------+------------+-------+
| Date/Time  | CURDATE()  | Date  |
+------------+------------+-------+
| 2005-05-25 | 2018-06-25 | -4779 |
+------------+------------+-------+

MySQL: Datetime Versus Timestamp Data Types

The temporal data types in MySQL can be confusing. Hopefully, this example and discussion will help to explain the differences in the timestamp and datetime data types.

From the MySQL reference:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

A major difference between these two data types is that TIMESTAMP data type values are converted from current time zone to UTC for storage purpose and converted back from UTC to current time zone when used. The datetime data type values are unchanged in relation to time zone.

This example is a good exercise in demonstrating the difference between these two data types.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    |  Value              |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+
2 rows in set (0.00 sec)

You can see our current time zone information. Under this environment, let us create a table with the two data types and populate it with the same temporal information.

create table datedemo
(
 mydatetime datetime,
 mytimestamp timestamp
);

Query OK, 0 rows affected (0.05 sec)

insert into datedemo values ((now()), (now()));

Query OK, 1 row affected (0.02 sec)

select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

At this point the datetime and timestamp data types have remained the exact same values. Let us change the time zone see the results.

SET TIME_ZONE = "america/new_york";

Query OK, 0 rows affected (0.00 sec)

 select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
1 row in set (0.00 sec)

The above example shows how the TIMESTAMP date type changed the values after changing the time-zone to ‘america/new_work’ where DATETIME is unchanged.

Working with date and time in MySQL + PHP

Below is an example that uses date functions. The above query selects all records with the date_col value within the last 30 days:

SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK (date) - Returns the index of the day of the week for the argument date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values ​​correspond to the ODBC standard.

SELECT DAYOFWEEK('1998-02-03'); // -> 3

WEEKDAY (date) - Returns the index of the day of the week for the argument date (0 = Monday, 1 = Tuesday, ... 6 = Sunday):

SELECT WEEKDAY('1998-02-03 22:23:00'); // -> 1
SELECT WEEKDAY('1997-11-05'); // -> 2

DAYOFMONTH (date) - Returns the ordinal number of the day of the month for the date argument in the range from 1 to 31:

SELECT DAYOFMONTH('1998-02-03'); // -> 3

DAYOFYEAR (date) - Returns the ordinal number of the year of the year for the date argument in the range from 1 to 366:

SELECT DAYOFYEAR('1998-02-03'); // -> 34

MONTH (date) - Returns the month ordinal of the year for the date argument in the range from 1 to 12:

SELECT MONTH('1998-02-03'); // -> 2

DAYNAME (date) - Returns the name of the day of the week for the date argument:

SELECT DAYNAME("1998-02-05"); // -> 'Thursday'

MONTHNAME (date) - Returns the name of the month for the date argument:

SELECT MONTHNAME("1998-02-05"); // -> 'February'

QUARTER (date) - Returns the quarter number of the year for the date argument in the range from 1 to 4:

SELECT QUARTER('98-04-01'); // -> 2

WEEK (date), WEEK (date, first) - If there is one argument, returns the week ordinal of the year for date in the range from 0 to 53 (yes, perhaps the beginning of the 53rd week) for regions where Sunday is considered the first day of the week. The WEEK () form with two arguments allows you to specify from which day the week starts - from Sunday or from Monday. The result will be in the range 0-53 or 1-52.

Here's how the second argument works: 
0 - Week starts on Sunday; the return value is in the range 0-53 
1 - Week starts on Monday; the return value is in the range 0-53 
2 - Week starts on Sunday; the return value is in the range 1-53 
3 - Week starts on Monday; the return value is in the range 1-53 (ISO 8601)

SELECT WEEK('1998-02-20'); // -> 7
SELECT WEEK('1998-02-20',0); // -> 7
SELECT WEEK('1998-02-20',1); // -> 8
SELECT WEEK('1998-12-31',1); // -> 53

Note: in version 4.0 WEEK (#, 0) function has been changed to meet the US calendar. 
Note, if the week is the last week of last year, MySQL will return 0 if you did not specify 2 or 3 as an optional argument:

SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); // -> 2000, 0
SELECT WEEK('2000-01-01',2); // -> 52

We can assume that MySQL should return 52, since this date is the 52nd week of the year 1999. We decided to return 0, since we want the function to give "the week number in the specified year". This makes the WEEK () function more reliable when used in conjunction with other functions that compute parts of dates.

If you still need to clarify the correct week in a year, then you can use 2 or 3 as an optional argument or use YEARWEEK ()

SELECT YEARWEEK('2000-01-01'); // -> 199952
SELECT MID(YEARWEEK('2000-01-01'),5,2); // -> 52

YEAR (date) - Returns the year for the date argument in the range from 1000 to 9999:

SELECT YEAR('98-02-03'); // -> 1998

YEARWEEK (date), YEARWEEK (date, first) - Returns the year and week for the date argument. The second argument in this function works like the second argument in the WEEK () function. Note that the year may differ from the date specified in the date argument for the first and last weeks of the year:

SELECT YEARWEEK('1987-01-01'); // -> 198653

Note that the week number is different from the one returned by the WEEK () (0) function, being called with the optional argument 0 or 1. This is because WEEK () returns the week number in the specified year. 

HOUR (time) - Returns the hour for the time argument in the range from 0 to 23:

SELECT HOUR('10:05:03'); //-> 10

MINUTE (time) - Returns the number of minutes for the time argument in the range 0 to 59:

SELECT MINUTE('98-02-03 10:05:03'); // -> 5

SECOND (time) - Returns the number of seconds for the time argument in the range from 0 to 59:

SELECT SECOND('10:05:03'); // -> 3

PERIOD_ADD (P, N) - Adds N months to period P (in YYMM or YYYYMM format). Returns the value in YYYYMM format. Note that the argument of period P is not a date value:

SELECT PERIOD_ADD(9801,2); // -> 199803

PERIOD_DIFF (P1, P2) - Returns the number of months between periods P1 and P2. P1 and P2 must be in YYMM or YYYYMM format. Note that the arguments of period P1 and P2 are not date values:

SELECT PERIOD_DIFF(9802,199703); // -> 11

DATE_ADD (date, INTERVAL expr type), DATE_SUB (date, INTERVAL expr type), ADDDATE (date, INTERVAL expr type), SUBDATE (date, INTERVAL expr type) - These functions perform arithmetic operations on dates. Both are a new version of MySQL 3.22. The ADDDATE () and SUBDATE () functions are synonyms for DATE_ADD () and DATE_SUB (). In MySQL version 3.23, instead of the DATE_ADD () and DATE_SUB () functions, you can use the + and - operators if the expression on the right side is a column of type DATE or DATETIME (see the example below). The date argument is a value of type DATETIME or DATE, which specifies the start date.

The expression expr specifies the amount of the interval to be added to the start date or subtracted from the start date. The expression expr is a string that can begin with - for negative values ​​of intervals. The type keyword shows how to interpret this expression. The auxiliary function EXTRACT (type FROM date) returns the interval of the specified type (type) from the date value. The following table shows the relationship between type and expr:

SECOND, - SECONDS 
MINUTE - MINUTES 
HOUR - HOURS 
DAY - DAYS 
on MONTH - in MONTHS 
the YEAR - YEARS 
MINUTE_SECOND - "MINUTES: SECONDS" 
HOUR_MINUTE - "HOURS: MINUTES" 
DAY_HOUR - "DAYS HOURS" 
YEAR_MONTH - "YEARS-in MONTHS" 
HOUR_SECOND - "HOURS: MINUTES: SECONDS " 
DAY_MINUTE -" DAYS HOURS: MINUTES " 
DAY_SECOND -" DAYS HOURS: MINUTES: SECONDS "

In MySQL, the format of the expr expression allows any separator characters. The separators shown in this table are given as examples. If the date argument is a DATE type value and the supposed calculations include only the parts YEAR, MONTH, and DAY (ie do not contain the time part of TIME), the result is represented as a value of type DATE. In other cases, the result is the DATETIME value:

SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; // -> 1998-01-01 00:00:00
SELECT INTERVAL 1 DAY + "1997-12-31"; // -> 1998-01-01
SELECT "1998-01-01" - INTERVAL 1 SECOND; // -> 1997-12-31 23:59:59
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); // -> 1998-01-01 00:00:00
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY); // -> 1998-01-01 23:59:59
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND); // -> 1998-01-01 00:01:00
SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND); // -> 1997-12-30 22:58:59
SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); // -> 1997-12-30 14:00:00
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); // -> 1997-12-02

If the specified interval is too short (i.e., does not include all parts of the interval expected with the specified keyword type), then MySQL assumes that the leftmost parts of the interval are omitted. For example, if the argument type is specified as DAY_SECOND, then the expected expression expr should have the following parts: days, hours, minutes and seconds. If in this case you specify the value of the interval as "1:10", MySQL assumes that the days and hours are omitted, and this value includes only minutes and seconds. In other words, the combination "1:10" DAY_SECOND is interpreted as the equivalent of "1:10" MINUTE_SECOND. Similarly, MySQL interprets TIME values ​​- more as representing the elapsed time than as the time of day. Should be considered,

SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY); // -> 1999-01-02
SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR); // -> 1999-01-01 01:00:00

If you use incorrect date values, the result will be NULL. If, when summing MONTH, YEAR_MONTH, or YEAR, the day number in the resulting date exceeds the maximum number of days in the new month, then the day of the resulting date is taken as the last day of the new month:

SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH); // -> 1998-02-28

From the previous example, we see that the word INTERVAL and the type keyword are not case-sensitive.

EXTRACT (type FROM date) - The interval types for the EXTRACT () function are the same as for the DATE_ADD () or DATE_SUB () functions, but EXTRACT () extracts the part from the date value rather than performing arithmetic operations.

SELECT EXTRACT(YEAR FROM "1999-07-02"); // -> 1999
SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); // -> 199907
SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); // -> 20102

TO_DAYS (date) - function returns the day number for the date specified in the date argument (number of days since year 0):

SELECT TO_DAYS(950501); // -> 728779
SELECT TO_DAYS('1997-10-07'); // -> 729669

The TO_DAYS () function is not intended to be used with values ​​preceding the introduction of the Gregorian calendar (1582), since it does not take into account the days lost when the calendar is changed.

FROM_DAYS (N) - Returns the DATE value for the given day number N:

SELECT FROM_DAYS(729669); // -> '1997-10-07'

The FROM_DAYS () function is not intended for use with values ​​preceding the introduction of the Gregorian calendar (1582), since it does not take into account the days lost when the calendar is changed.

DATE_FORMAT (date, format) - Format the date value according to the format string. In the format string, the following qualifiers can be used:% M The name of the month (January ... December) 
% W The name of the day of the week (Sunday ... Saturday) 
% D The day of the month with the English suffix (0st, 1st, 2nd, 3rd, etc .) 
% Y Year, date, 4 digits 
% y Year, date, 2 digits 
% X Year for the week where Sunday is the first day of the week, number, 4 digits, used with '% V' 
% x Year for the week where Sunday is the first day of the week, the number is 4 digits, is used with '% v' 
% a Abbreviated name of the day of the week (Sun ... Sat) 
% d Day of the month, day (00..31) 
% e Day of the month, number (0. .31) 
% m Month, number (00.1 2)
% c Month, number (0..12) 
% b Abbreviated month name (Jan ... Dec) 
% j Day of the year (001..366) 
% H Hour (00..23) 
% k Hour (0..23 ) 
% h Hour (01..12) 
% I Hour (01..12) 
% l Hour (1..12) 
% i Minutes, number (00..59) 
% r Time, 12 hours format (hh: mm: ss [AP] M) 
% T Time, 24h format (hh: mm: ss) 
% S Seconds (00..59) 
% s Seconds (00..59) 
% p AM or PM 
% w Day of the week (0 = 
Sunday..6 = Saturday) % U Week (00..53), where Sunday is considered the first day of the week 
% u Week (00..53), where Monday is the first day of the week 
% V Week (01..53 ), where Sunday is considered the first day of the week. Used with '% X'
% v Week (01..53), where Monday is the first day of the week. Used with '% x' 
%% Literals '%'.

All other symbols are simply copied into the resulting expression without interpretation:

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); // -> 'Saturday October 1997'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); // -> '22:23:00'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); // -> '4th 97 Sat 04 10 Oct 277'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); // -> '22 22 10 10:23:00 PM 22:23:00 00 6'
SELECT DATE_FORMAT('1999-01-01', '%X %V'); // -> '1998 52'
SELECT DATE_FORMAT(sale.time, '%Y-%m-%d') as dat // 2014-03-11

In MySQL 3.23, the character '%' must precede the symbols of the format identifier. In earlier versions of MySQL, the character '%' is optional.

The reason that the intervals for the month and day start from zero is that MySQL allows you to use incomplete dates, such as '2004-00-00', starting with MySQL 3.23.

TIME_FORMAT (time, format) - This function is used similarly to the function DATE_FORMAT () described above, but the format string can only contain format specifiers that refer to hours, minutes and seconds. When specifying other determinants, the value NULL or 0 will be returned.

CURDATE () , CURRENT_DATE - Returns today's date as a value in the format YYYY-MM-DD or YYYYMMDD, depending on the context in which the function is used - in a string or numeric:

SELECT CURDATE(); // -> '1997-12-15'
SELECT CURDATE() + 0; //-> 19971215

CURTIME (), CURRENT_TIME - Returns the current time as a value in the format HH: MM: SS or HHMMS, depending on the context in which the function is used - in a string or numeric:

SELECT CURTIME(); // -> '23:50:26'
SELECT CURTIME() + 0; // -> 235026

NOW (), SYSDATE (), CURRENT_TIMESTAMP - Returns the current date and time as a value in the format YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric:

SELECT NOW(); // -> '1997-12-15 23:50:26'
SELECT NOW() + 0; // -> 19971215235026

Note that NOW () is evaluated only once for each request, namely, at the beginning of its execution. This allows you to be sure that multiple links to NOW () within the same query will give the same value.

UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date) - When this function is called without an argument, it returns the UNIX_TIMESTAMP timestamp (seconds from 1970-01-01 00:00:00 GMT) as an unsigned integer. If the UNIX_TIMESTAMP () function is called with the date argument, it returns the argument value as the number of seconds from 1970-01-01 00:00:00 GMT. The date argument can be a DATE, a DATETIME, a TIMESTAMP type, or a YYMMDD or YYYYMMDD local time:

SELECT UNIX_TIMESTAMP(); // -> 882226357
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); // -> 875996580

When using the UNIX_TIMESTAMP function in the TIMESTAMP column, this function will return the value of the internal timestamp directly, without implicit conversion of the string to a timestamp (`` string-to-unix-timestamp ''). If the specified date is outside the allowed range, the UNIX_TIMESTAMP () function will return 0, but note that only the basic check is performed (year 1970-2037, month 01-12, day 01-31). If you want to subtract UNIX_TIMESTAMP () columns, you can convert the result to signed integers. See Section 6.3.5, "Type casting functions".

FROM_UNIXTIME (unix_timestamp) - Returns the representation of the unix_timestamp argument as a value in the format YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric:

SELECT FROM_UNIXTIME(875996580); // -> '1997-10-04 22:23:00'
SELECT FROM_UNIXTIME(875996580) + 0; // -> 19971004222300

FROM_UNIXTIME (unix_timestamp, format) - Returns the string representation of the unix_timestamp argument, formatted according to the format string. The format string can contain the same qualifiers that are listed in the description for the function DATE_FORMAT ():

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); // -> '1997 23rd December 03:43:30 1997'

SEC_TO_TIME (seconds) - Returns the seconds argument, converted to hours, minutes and seconds, as a value in the format HH: MM: SS or HHMMSS, depending on whether the function is used in a string or numeric:

SELECT SEC_TO_TIME(2378); // -> '00:39:38'
SELECT SEC_TO_TIME(2378) + 0; // -> 3938

TIME_TO_SEC (time) - Returns the time argument converted to seconds:

SELECT TIME_TO_SEC('22:23:00'); // -> 80580
SELECT TIME_TO_SEC('00:39:38'); // -> 2378