The TIMEDIFF SQL Function in MySQL and MariaDB - Time difference |
|
| | TIMEDIFF | Syntax: | TIMEDIFF(Date 1, Date 2) | | Return value: | TIME | | Function type: | Date and Time function | |
| | The TIMEDIFF() function returns "Date 1" - "Date 2" expressed as a time value (TIME).
The "Date 1" and "Date 2" arguments are TIME or DATETIME expressions. Both arguments must be of the same type. If not, the function returns NULL.
If the value of "Date 1" or "Date 2" is NULL or an empty string, the TIMEDIFF() function returns NULL.
Invalid values are interpreted as time value 00:00:00 under MariaDB. MySQL 8.0, on the other hand, returns NULL. | Examples for MariaDB 10.3 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
| select timediff('10:35:10','09:10:05');
select timediff('10:35:10','09:10:05.099');
select timediff('2023-03-10 10:35:10','2023-03-10 09:10:05');
select timediff('2023-03-10 10:35:10','2023-00-10 09:10:05');
select timediff(now(),now());
select timediff(now(),utc_timestamp());
select timediff('2023-03-10 10:35:10','09:10:05');
select timediff('10:35:10','xxx'), timediff('10:35:10',''), timediff('10:35:10','00:00:00');
select timediff('xxx','10:35:10'), timediff('','10:35:10'), timediff('00:00:00','10:35:10');
select timediff(null,now());
select timediff(now(),null);
|
|
| timediff('10:35:10','09:10:05') |
|---|
| time(10) |
|---|
| 01:25:05 |
|
|
| timediff('10:35:10','09:10:05.099') |
|---|
| time(14) |
|---|
| 01:25:04.901 |
|
|
| timediff('2023-03-10 10:35:10','2023-03-10 09:10:05') |
|---|
| time(10) |
|---|
| 01:25:05 |
|
|
| timediff('2023-03-10 10:35:10','2023-00-10 09:10:05') |
|---|
| time(10) |
|---|
| 838:59:59 |
|
|
| timediff(now(),now()) |
|---|
| time(10) |
|---|
| 00:00:00 |
|
|
| timediff(now(),utc_timestamp()) |
|---|
| time(10) |
|---|
| 02:00:00 |
|
|
| timediff('2023-03-10 10:35:10','09:10:05') |
|---|
| time(10) |
|---|
| NULL |
|
|
| timediff('10:35:10','xxx') |  |
|
| timediff('10:35:10','') | |
| timediff('10:35:10','00:00:00') |
|---|
| time(10) | time(17) | time(10) |
|---|
| 10:35:10 | NULL | 10:35:10 |
|
|
|
|
| timediff('xxx','10:35:10') | |
|
| timediff('','10:35:10') | |
| timediff('00:00:00','10:35:10') |
|---|
| time(10) | time(17) | time(10) |
|---|
| -10:35:10 | NULL | -10:35:10 |
|
|
|
|
| timediff(null,now()) |
|---|
| time(10) |
|---|
| NULL |
|
|
| timediff(now(),null) |
|---|
| time(10) |
|---|
| NULL |
|
|
| Examples for MySQL 8.0 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
| select timediff('10:35:10','09:10:05');
select timediff('10:35:10','09:10:05.099');
select timediff('2023-03-10 10:35:10','2023-03-10 09:10:05');
select timediff('2023-03-10 10:35:10','2023-00-10 09:10:05');
select timediff(now(),now());
select timediff(now(),utc_timestamp());
select timediff('2023-03-10 10:35:10','09:10:05');
select timediff('10:35:10','xxx'), timediff('10:35:10',''), timediff('10:35:10','00:00:00');
select timediff('xxx','10:35:10'), timediff('','10:35:10'), timediff('00:00:00','10:35:10');
select timediff(null,now());
select timediff(now(),null);
|
|
| timediff('10:35:10','09:10:05') |
|---|
| time(10) |
|---|
| 01:25:05 |
|
|
| timediff('10:35:10','09:10:05.099') |
|---|
| time(14) |
|---|
| 01:25:04.901 |
|
|
| timediff('2023-03-10 10:35:10','2023-03-10 09:10:05') |
|---|
| time(10) |
|---|
| 01:25:05 |
|
|
| timediff('2023-03-10 10:35:10','2023-00-10 09:10:05') |
|---|
| time(10) |
|---|
| 838:59:59 |
|
|
| timediff(now(),now()) |
|---|
| time(10) |
|---|
| 00:00:00 |
|
|
| timediff(now(),utc_timestamp()) |
|---|
| time(10) |
|---|
| 02:00:00 |
|
|
| timediff('2023-03-10 10:35:10','09:10:05') |
|---|
| time(10) |
|---|
| NULL |
|
|
| timediff('10:35:10','xxx') | |
|
| timediff('10:35:10','') | |
| timediff('10:35:10','00:00:00') |
|---|
| time(17) | time(17) | time(10) |
|---|
| NULL | NULL | 10:35:10 |
|
|
|
|
| timediff('xxx','10:35:10') | |
|
| timediff('','10:35:10') | |
| timediff('00:00:00','10:35:10') |
|---|
| time(17) | time(17) | time(10) |
|---|
| NULL | NULL | -10:35:10 |
|
|
|
|
| timediff(null,now()) |
|---|
| time(10) |
|---|
| NULL |
|
|
| timediff(now(),null) |
|---|
| time(10) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the TIMEDIFF() function in MySQL and MariaDB databases | | The TIMEDIFF() function in MySQL and MariaDB calculates the difference between two "time" or "Date and time" expressions. The difference is returned as a time value in the format "HH:MM:SS". The parameters can be time, datetime, or timestamp values. These can be columns, constants or expressions that result in such values and only the time differences are calculated and the date components are not taken into account. The function is useful for calculating time differences between two values so that various calculations can be performed or the duration between events can be displayed. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | FROM_UNIXTIME | FROM_UNIXTIME(Unix timestamp [, Format]) | | More about FROM_UNIXTIME Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | STR_TO_DATE | STR_TO_DATE(String, Format) | | More about STR_TO_DATE Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMESTAMP | TIMESTAMP(Date [, Addition]) | | More about TIMESTAMP Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | EXTRACT | EXTRACT(Unit FROM Date) | | More about EXTRACT Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
| | TIME_FORMAT | TIME_FORMAT(Time, Format) | | More about TIME_FORMAT Function |
|
|
| | | More information about the TIMEDIFF SQL function: and and |
|
|
|
|
