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'
The exercise can be solved with built-in function DATEDIFF:
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.
The result “1 day” would be produced even if the dates differ with one second:
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:
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:
To get the time interval one can take a difference of two temporal values. An explicit type conversion is required in this case:
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:
The following function can be used to get the number of seconds in the time interval:
The number of seconds must be divided by 60. So the final result can be produced with the following statement:
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:
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):
In a similar way, it could be gotten other time intervals.