Table 8.3. Time Format Characters Used by DATE_FORMAT()
Extracting Part of a Date
You can use the EXTRACT() function to return part of the date. Similar behavior can be achieved using DATE_FORMAT(), but whereas that function returns a formatted string, EXtrACT() returns a numeric result.
The syntax of EXtrACT() uses the same interval specifier as date arithmetic, with the keyword FROM. The following example returns the day number part of the current date:
MariaDB> SELECT EXTRACT(DAY FROM NOW()); +-------------------------+ | EXTRACT(DAY FROM NOW()) | +-------------------------+ | 28 | +-------------------------+ 1 row in set (0.01 sec)
If you use a compound unit, the values are returned as a single number with the most significant value first. The following example returns the year and month from the current date:
MariaDB> SELECT EXTRACT(YEAR_MONTH FROM NOW()); +--------------------------------+ | EXTRACT(YEAR_MONTH FROM NOW()) | +--------------------------------+ | 200510 | +--------------------------------+ 1 row in set (0.00 sec)
Date Arithmetic
To compute the difference between two dates, use the DATEDIFF() function. Given two date arguments, DATEDIFF() returns the number of days between the second date and the first.
MariaDB> SELECT DATEDIFF('2006-01-20', '2005-11-10');
+--------------------------------------+
| DATEDIFF('2006-01-20', '2005-11-10') |
+--------------------------------------+
| 71 |
+--------------------------------------+
1 row in set (0.00 sec)
If the second date is greater than the first, the value returned is negative, as shown in this example:
MariaDB> SELECT DATEDIFF('2005-12-31', '2006-01-01');
+--------------------------------------+
| DATEDIFF('2005-12-31', '2006-01-01') |
+--------------------------------------+
| -1 |
+--------------------------------------+
1 row in set (0.00 sec)
You can use TIMEDIFF() to calculate the difference between two times. The result returned is a time value, as shown in the following example:
MariaDB> SELECT TIMEDIFF('12:00', '10:30');
+----------------------------+
| TIMEDIFF('12:00', '10:30') |
+----------------------------+
| 01:30:00 |
+----------------------------+
1 row in set (0.02 sec)
The arguments to TIMEDIFF() contain date elements, but a time value is still returnedthere is not a date component in the result. The following example shows that when the times are more than a day apart, the time value returned is larger than 24 hours:
MariaDB> SELECT TIMEDIFF('2006-01-31 12:00', '2006-01-30 09:30');
+--------------------------------------------------+
| TIMEDIFF('2006-01-31 12:00', '2006-01-30 09:30') |
+--------------------------------------------------+
| 26:30:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
UNIX Time Stamps
UNIX time stamp format is a common way of representing time values as an integer. The numeric value is the number of seconds since midnight on January 1, 1970. The current time stamp value is a 10-digit number.
The UNIX_TIMESTAMP() function returns the time stamp value of a date. If it is called with no arguments, it returns the current time stamp.
MariaDB> SELECT UNIX_TIMESTAMP('2006-01-01 12:45:31');
+---------------------------------------+
| UNIX_TIMESTAMP('2006-01-01 12:45:31') |
+---------------------------------------+
| 1136119531 |
+---------------------------------------+
1 row in set (0.00 sec)
To convert a time stamp to a MariaDB date, use FROM_UNIXTIME().
MariaDB> SELECT FROM_UNIXTIME(1136119531); +---------------------------+ | FROM_UNIXTIME(1136119531) | +---------------------------+ | 2006-01-01 12:45:31 | +---------------------------+ 1 row in set (0.00 sec)
|
Character |
Meaning | |
|---|---|---|
|
%H |
Hour on 24-hour clock, two digits (0023) | |
|
%h |
Hour on 12-hour clock (0112) | |
|
%i |
Minutes, two digits (0159) | |
|
%l |
Hour on 12-hour clock, no leading zero (112) | |
|
%p |
A.M. or P.M. | |
|
%r |
Time on 12-hour clock as HH:MM:SS | |
|
%s |
Seconds, two digits (0059) | |
|
%T |
Time on 24-hour clock as HH:MM:SS | |
| ||
| ||
  
|