0

I am trying to calculate the number of minutes between two datetimes using sql ansi-92. I'm not able to use timestampdiff() function because I get an error response.

I tried date1-date2, but I always get a result of 0 (although no error).

Example: 

select '2016-11-02 15:25:00' - '2016-11-02 15:05:00'
massko
  • 589
  • 1
  • 7
  • 22
chris
  • 1
  • 2
  • What SQL engine are you using? Why do you think it should support that function? – ajeh Nov 03 '16 at 15:19
  • @ajeh: they are using Standard SQL-92 and the spec states, "Arithmetic operations involving items of type datetime or interval obey the natural rules associated with dates and times and yield valid datetime or interval results according to the Gregorian calendar...and returns an exact numeric value representing the value of one component of the datetime or interval." – onedaywhen Nov 03 '16 at 15:26
  • I assume the time granule is defaulting to `DAY` i.e. difference in days is zero. – onedaywhen Nov 03 '16 at 15:27
  • Are you telling me that SQL-92 is an engine? I thought it was a standard. – ajeh Nov 03 '16 at 17:31
  • @ajeh: you are the only one talking about an 'engine'. I figure the OP is interested in ISO Standard SQL-92 compliant *code*. Perhaps [Mimer's SQL-92 Validator](http://developer.mimer.com/validator/parser92/index.tml) meets your requirement of an 'engine' to be able to test your code? – onedaywhen Nov 04 '16 at 08:17
  • Oh, my bad, I thought that he was trying to run a statement and was getting an error, but initially did not tell us what engine he was in. Then he added `mysql` tag and got an answer from massko. – ajeh Nov 04 '16 at 14:23

1 Answers1

0

That certainly should work for you:

select TIMESTAMPDIFF(MINUTE,a.my_date2,a.my_date) diff,
       TIMESTAMPDIFF(MINUTE,'2016-11-02 15:05:00','2016-11-02 15:25:00') diff2
from my_tab a

I got the output (difference in minutes):

| diff | diff2 |
|------|-------|
|   20 |    20 |

I used this code to create table if you need it:

create table my_tab
(
  my_date datetime,
  my_date2 datetime
  )
 ;
 insert into my_tab (my_date, my_date2)
 values ('2016-11-02 15:25:00', '2016-11-02 15:05:00')
 ;
massko
  • 589
  • 1
  • 7
  • 22