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