DATEDIFF()参数问题 [英] DATEDIFF() Parameter Issue
问题描述
SELECT DATEDIFF(hour,CURDATE ,(SELECT Post_Date FROM Post_T WHERE pk_PostID = 1))
有一种简单的方法可以返回小时值吗?
另外我使用的是MYSQL版本5.5.20。
就像在文档:
DATEDIFF(expr1,expr2)
DATEDIFF()返回expr1 - expr2表示为天中的值,从一个
日期到另一个
日期。 expr1和expr2是日期或日期和时间
表达式。
计算中仅使用值的日期部分。
如果您希望在几个小时内使用结果,您应该使用 Timestampdiff
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回datetime_expr2 - datetime_expr1,其中datetime_expr1和
datetime_expr2是日期或日期时间表达式。一个表达式可能是
一个日期,另一个表示可以是日期时间;在必要时,日期值被视为具有时间部分00:00:00的日期时间
。
结果(整数)的单位由单位参数给出。
单位参数可以是: code> MICROSECOND(微秒),SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER或YEAR。
你可以这样做:
SELECT TIMESTAMPDIFF(hour,CURDATE(),(SELECT Post_Date FROM Post_T WHERE pk_PostID = 1))
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.
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) )
这篇关于DATEDIFF()参数问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!