Showing posts with label Mysql timediff. Show all posts
Showing posts with label Mysql timediff. Show all posts

Tuesday, 30 July 2019

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 TIMEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?

This article looks at the difference between the MySQL TIMEDIFF() and TIMESTAMPDIFF() functions.
Both functions do a similar thing, but there are some significant differences between the two.
The following table summarizes the difference between these two functions:
TIMEDIFF()TIMESTAMPDIFF()
Requires 2 arguments.Requires 3 arguments.
Subtracts the 2nd argument from the 1st (date1 − date2).Subtracts the 2nd argument from the 3rd (date2 − date1).
Result is expressed as a time value (and it has the limitations of the time data type).Result is an integer, expressed by a number of units as provided by the first argument.
Accepts time or datetime expressions.Accepts date or datetime expressions.
Both arguments must be the same type (either time or datetime).Both arguments can be of a different type (date or datetime).

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 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'TIMESTAMPDIFF';
Result:
+----------+---------------+
| TIMEDIFF | TIMESTAMPDIFF |
+----------+---------------+
| 24:00:00 |           -24 |
+----------+---------------+
So we can see that TIMEEDIFF() returned a time value, and TIMESTAMPDIFF() returned an integer.
Also, TIMEEDIFF() subtracted the 2nd date from the 1st, while TIMESTAMPDIFF()subtracted the 1st date from the 2nd.

Example 2 – Changing the Unit

As mentioned, TIMESTAMPDIFF() allows us to specify which unit to represent the result in. Here are some examples:
SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds';
Result:
+----------+-------+---------+---------+
| TIMEDIFF | Hours | Minutes | Seconds |
+----------+-------+---------+---------+
| 36:15:35 |   -36 |   -2175 | -130535 |
+----------+-------+---------+---------+
However, here’s what happens if we use a unit that’s larger than the actual time difference:
SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(week, @date1, @date2) AS 'Weeks',
  TIMESTAMPDIFF(month, @date1, @date2) AS 'Months';
Result:
+----------+------+-------+--------+
| TIMEDIFF | Days | Weeks | Months |
+----------+------+-------+--------+
| 36:15:35 |   -1 |     0 |      0 |
+----------+------+-------+--------+
In this case, the time difference wasn’t big enough to affect the week or month 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 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF Date',
  TIMESTAMPDIFF(hour, @time1, @time2) AS 'TIMESTAMPDIFF Time';
Result:
+---------------+--------------------+
| TIMEDIFF Date | TIMESTAMPDIFF Time |
+---------------+--------------------+
| 00:00:00      |               NULL |
+---------------+--------------------+
The TIMEDIFF() doesn’t support the ‘date’ data type, and so it returns 00:00:00.
And the TIMESTAMPDIFF() function doesn’t support the ‘time’ data type, so it returns NULL.

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 
  TIMEDIFF(@thetime, @thedatetime) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @thedate, @thedatetime) AS 'TIMESTAMPDIFF';
Result:
+----------+---------------+
| TIMEDIFF | TIMESTAMPDIFF |
+----------+---------------+
| NULL     |           -24 |
+----------+---------------+
So we can see that TIMESTAMPDIFF() 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 datetime).
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 |
+----------+----------+
So it’s fine, as long as both arguments are the same type (either time or datetime values).

MySQL - TIMEDIFF() Examples

The MySQL TIMEDIFF() function returns the difference between two time or datetime values.

The way it works is, you provide the two values to compare, and TIMEDIFF() subtracts the second value from the first, then returns the result as a time value.

Syntax

The syntax goes like this:
TIMEDIFF(expr1,expr2)
Where expr1 and expr2 are the two values to compare. The return value is expr2subtracted from expr1.

Basic Example

Here’s an example to demonstrate.
SELECT TIMEDIFF('11:35:25', '10:35:25');
Result:
+----------------------------------+
| TIMEDIFF('11:35:25', '10:35:25') |
+----------------------------------+
| 01:00:00                         |
+----------------------------------+

Elapsed Time

The time value can represent elapsed time, so it’s not limited to being less than 24 hours.
SELECT TIMEDIFF('500:35:25', '10:35:25');
Result:
+-----------------------------------+
| TIMEDIFF('500:35:25', '10:35:25') |
+-----------------------------------+
| 490:00:00                         |
+-----------------------------------+

Negative Time Difference

If the second value is larger than the first, you’ll get a negative value for the time difference. This is perfectly valid.
SELECT TIMEDIFF('10:35:25', '500:35:25');
Result:
+-----------------------------------+
| TIMEDIFF('10:35:25', '500:35:25') |
+-----------------------------------+
| -490:00:00                        |
+-----------------------------------+

Datetime Values

Here’s an example that uses datetime values as the arguments.
SELECT TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25');
Result:
+--------------------------------------------------------+
| TIMEDIFF('2021-02-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| 744:00:00                                              |
+--------------------------------------------------------+
Note that both arguments must be of the same type. So you can’t have a time value for the first and a datetime value for the second (and vice-versa).
Also note that the time data type can only be in the range -838:59:59 to 838:59:59. Therefore, the following doesn’t work:
SELECT TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25');
Result:
+--------------------------------------------------------+
| TIMEDIFF('2000-01-01 10:35:25', '2021-01-01 10:35:25') |
+--------------------------------------------------------+
| -838:59:59                                             |
+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
In this case, we get an incorrect result and a warning.

Thursday, 8 November 2018

mysql timediff to hours

I'm Trying to get the timediff from my table and convert it to hours (it's for an hourly billed service)
SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600 FROM tasks >
where endDate and startDate are in datetime format
is there another way (more efficient) to do this task? 

 Answers


TIMEDIFF(endDate, startDate) outputs in DateTime format, so flat that to timestamp and devide by (60*60)
SELECT (UNIX_TIMESTAMP(TIMEDIFF(endDate, startDate))/(60*60)) AS hours_difference
FROM tasks
Edit: Alternatively,TimestampDiff may also provide a valid solution in more elegant way providing its example:
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
And your solution can be:
SELECT TIMESTAMPDIFF(HOUR, endDate, startDate) AS hours_different
FROM tasks



HOUR(TIMEDIFF(endDate, startDate))
might work—if I'm reading the docs correctly.


eg: startDate 2010-01-31 00:00:00, endDate 2010-01-31 19:24:22
SELECT (UNIX_TIMESTAMP(dateFin)-UNIX_TIMESTAMP(dateDebut))/3600 hour_diff
FROM tasks

SELECT TIME_TO_SEC(TIMEDIFF(endDate,startDate))/3600
FROM tasks
returns 19.4061 which is good
SELECT TIMESTAMPDIFF(HOUR, endDate, startDate) AS hours_different
FROM tasks
Only returns hours while i also need minutes to be converted.
SELECT (UNIX_TIMESTAMP(TIMEDIFF(endDate, startDate))/(60*60)) AS hours_difference
FROM tasks
returns 0. I think the first one is the most efficent. Thanks !!


You can use UNIX_TIMESTAMP to do the calculation in SELECT query.
SELECT (UNIX_TIMESTAMP(endDate)-UNIX_TIMESTAMP(startDate))/3600 hour_diff
  FROM tasks
UNIX_TIMESTAMP convert datetime to number of second from epoch. You can substract both timestamp to get difference in second. Divide it with 3600 will give you difference in hour.


TIMEDIFF(endDate, startDate) / 10000

Subscribe to: Posts (Atom)