Transact-SQL string and datetime functions

T-SQL Datetime functions

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:

Console

Execute

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

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

    Scheme

No scheme

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

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';

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.