DATEDIFF() Parameter Issue

Question

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.

Answer 1

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