10

The old DATEDIFF() allowed users to use 3 parameters, and I was trying to do this so I could get hours out of my DATEDIFF rather than days, (I'm trying to show hours since a post). In my database I'm using a TIMESTAMP and this line of code to pull a value, and obviously it doesn't work because I have the extra parameter. Once I remove the 'hour' or 'hh' the query runs and returns a value in days.

SELECT DATEDIFF(hour, CURDATE(), (SELECT Post_Date FROM Post_T WHERE pk_PostID = 1) )

Is there an easy way I can return the hourly value?

Also I'm using MYSQL Version 5.5.20.

Cœur
  • 37,241
  • 25
  • 195
  • 267
pcort
  • 419
  • 1
  • 6
  • 19
  • DATEDIFF does use 3 parameters: DATEDIFF(datepart, date1, date2). Datepart for hours is hour or hh. What's the issue you are experiencing? – Metaphor Sep 20 '13 at 20:52
  • As of 5.1 I think they changed it, http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff and it only takes two parameters. When I do either 'hour' or 'hh' in the query it returns with the error #1582 - Incorrect parameter count in the call to native function 'DATEDIFF' – pcort Sep 20 '13 at 20:55

1 Answers1

31

Like it says in the documentation:

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

If you want the result in hours you should use Timestampdiff

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns datetime_expr2 – datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument.

The unit argument can be: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

In your case you can do:

SELECT TIMESTAMPDIFF(hour, CURDATE(), (SELECT Post_Date FROM Post_T WHERE pk_PostID = 1) )
Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Thanks so much! The only issue that I ran into is that CURDATE() only takes the YYYY-MM-DD format and doesn't include any time, so it defaults to YYYY-MM-DD 00:00:00 which throws off what I'm trying to accomplish. This was easily remedied by using NOW() instead of CURDATE(). Thanks so much! – pcort Sep 20 '13 at 21:38
  • Glad it worked. I was going to suggest now() at first, but i see you got there already :) – Filipe Silva Sep 20 '13 at 21:39