Русский    English

DATEDIFF function page 2

Exercise. Count the number of minutes in the interval between the two dates '2011-10-07 23:43:00' and '2011-10-08 01:23:00'

SQL Server

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

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

The answer is 100 minutes.


The query uses the ISO standard date notation as a text string 'yyyy-mm-ddThh:mm:ss'. The notation is interpreted unambiguously by any DBMS brand with any database server settings.


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.

  1. SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08T01:23:00');
  2. SELECT DATEDIFF('2011-10-07', '2011-10-08');
  3. 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:

  1. 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  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server DATEDIFF:

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


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:

  1. SELECT timestamp '2011-10-08T01:23:00' - timestamp '2011-10-07T23:43:00';
or in standard notation:

  1. 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:

  1. 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:

  1. EXTRACT(EPOCH FROM < interval >)

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

  1. 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:

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


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):

  1. SELECT (CAST('2011-10-08 01:23:00' AS date) - CAST('2011-10-07 23:43:00' AS date))*24*60
  2. FROM dual;

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

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.