DATEDIFF function

Syntax:

DATEDIFF(datepart, startdate, enddate)

The function returns the interval of date and time elapsed between two specified dates - startdate and enddate. This interval may be meassured in different units. Available values of the argument datepart listed above for the DATEADD function.

Example 7.1.2

SELECT DATEDIFF(dd, (SELECT MIN(date)
FROM pass_in_trip
),
(SELECT MAX(date)
FROM pass_in_trip
)
);

Example 7.1.3

It should be noted that the departure time (time_out) and the landing time (time_in) are stored in the columns of datetime type in the Trip table. Note, SQL Server up to version 2005 had not temporal data types for storing the date or time separately. Because of this, when inserting only the time in the datetime column (for example, UPDATE trip SET time_out = ‘17:24:00’ WHERE trip_no=1123), the time will be supplemented by the default date value (‘1900-01-01’).

The simple solution

SELECT DATEDIFF(mi, time_out, time_in) dur
FROM trip
WHERE trip_no = 1123;

Firstly, the value obtained in such manner will be incorrect for the trips that depart in one day and land in another one. Secondly, it is unreliably to make any suggestions on a day that is only presented of necessity to correspond to datetime data type.How can we know that a plane lands in the next day? This knowledge comes from the subject area, which says the flight cannot be over 24 hours. So, if the landing time not more than the departure time, this is the case.

The second question: how do we calculate only the time apart from the day?

The  T-SQL DATEPART function could help here; we’ll talk about it in the following chapter.

There is one feature of implementation of DATEDIFF function you should take into account. We’ll begin with examples. First we’ll count number of weeks since Sunday 10-23-2005 till Saturday 10-29-2005. So,

SELECT DATEDIFF (wk, '20051023 00:00:00', '20051029 23:59:59');

The common sense tells us that this is a full week, however the above query gives 0. Now we shall take an interval since Saturday 29-10-2005 till Sunday 30-10-2005:

SELECT DATEDIFF(wk, '20051029', '20051030');

As a result we shall receive 1, i.e. one week. It is a time to give an explanation. The answer is in the DATEDIFF functionality that actually considers week as not a number of days, but as a number of transitions since Saturday on Sunday. If one keeps in mind this fact, then even the more surprising example will be clear :

SELECT DATEDIFF(wk, '20051029 23:59:00', '20051030 00:01:00');

Is it strange? Probably, but, as they say, who is notified - is armed. The same takes place and for other intervals. For example, the quantity of days gives us not a number of hours, divided by 24 (quantity of hours per day), but a number of transitions over midnight. Do you want acknowledgement? Please

SELECT DATEDIFF(dd, '20051029 23:59:00', '20051030 00:01:00');

As a result we receive one day. At the same time

SELECT DATEDIFF(dd, '20051029 00:00:00', '20051029 23:59:59');

If you want, you can continue experiments with other time intervals. Use our Console for that.

Suggested exercises: 76, 93

SQL Server

The exercise can be solved with built-in function DATEDIFF:

SELECT DATEDIFF (minute, '2011-10-07T23:43:00', '2011-10-08T01:23:00');

The answer is 100 minutes.

MySQL

MySQL also supports DATEDIFF function, but it has a different meaning. It takes two dates as arguments and computes the number of days between them. If a date is written in date-time format, only date component is used. That is why all the following queries produce the same result –1. The result will be positive if the first argument is larger than the second one.

SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08T01:23:00');  
SELECT DATEDIFF('2011-10-07', '2011-10-08');  
SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08');

The result “1 day” would be produced even if the dates differ with one second:

SELECT DATEDIFF('2011-10-07T23:59:59', '2011-10-08T00:00:00');

It is another built-in function TIMESTAMPDIFF that solves the problem. This function is an analogue of SQL Server DATEDIFF:

SELECT TIMESTAMPDIFF(minute, '2011-10-07T23:43:00', '2011-10-08T01:23:00');

PostgreSQL

PostgreSQL has no function analogous to DATEDIFF in SQL Server nor to TIMESTAMPDIFF in MySQL. It is possible to act the following way to solve the exercise:

1. to present the difference between two dates as a time interval;
2. to count the number of seconds in the interval;
3. to divide the value by 60.

To get the time interval one can take a difference of two temporal values. An explicit type conversion is required in this case:

SELECT timestamp '2011-10-08T01:23:00' - timestamp '2011-10-07T23:43:00';

or in standard notation:

SELECT cast('2011-10-08T01:23:00' AS timestamp) - cast('2011-10-07T23:43:00' AS timestamp);

The result is “01:40:00”, none other than the duration of one hour and forty minutes.

A built-in function AGE produces the same result. The function performs its arguments type conversion implicitly:

SELECT AGE ('2011-10-08T01:23:00', '2011-10-07T23:43:00');

The following function can be used to get the number of seconds in the time interval:

EXTRACT(EPOCH FROM < interval >)

The number of seconds must be divided by 60. So the final result can be produced with the following statement:

SELECT EXTRACT(EPOCH FROM AGE('2011-10-08T01:23:00', '2011-10-07T23:43:00'))/60;

In this case the result is an integer number because both of the time values contain zero second values. If values of seconds are greater than zero, a decimal number may be produced. For example the following statement produces 99.75:

SELECT EXTRACT(EPOCH FROM AGE ('2011-10-08T01:23:00', '2011-10-07T23:43:15'))/60;

Oracle

The function like DATEDIFF is absent from Oracle also. Besides, Oracle does not support standard dates representation used above.

We can calculate an time interval in minutes taking into account that substraction of dates (values of date data type) gives number of days in Oracle. Then to obtain interval in minutes we just need multiply this result by 24 (number of hours  within a day) and then by 60 (number of minutes within an hour):

select (cast('2011-10-08 01:23:00' as date) - cast('2011-10-07 23:43:00' as date))*24*60
from dual;

In a similar way, it could be gotten other time intervals.