TimeStamp( )函数, TimeStampAdd( )函数 , TimeStampDiff( )函数
官网:https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff
TIMESTAMP(expr
)
, TIMESTAMP(expr1
,expr2
)
expr
)expr1
,expr2
)With a single argument, this function returns the date or datetime expression expr
as a datetime value. With two arguments, it adds the time expression expr2
to the date or datetime expression expr1
and returns the result as a datetime value.
PS:只有一个参数时,当参数类型为date或datetime时,最终返回结果都是datetime
当有两个参数时,第2个参数是time类型,把第2个参数的值加到第一个参数的值上
mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00'
#个人理解,即参数1中的值经过参数值2中的时间后,返回对应的日期时间值 select timestamp('2019-11-7 12:23:22','3:14:14') as a, #2019年11月7日,12点23分22秒,过了3小时14分14秒后,现在的日期时间值 timestamp('2019-11-7 12:23:22','19:14:14') as b, # 这个经过19:14:14后,过了一天 timestamp('2019-11-7 7:23:22','19:14:14') as c;
TIMESTAMPADD(
unit
,interval
,datetime_expr
)
Adds the integer expression interval
to the date or datetime expression datetime_expr
. The unit for interval
is given by theunit
argument, which should be one of the following values: MICROSECOND
(microseconds), SECOND
, MINUTE
, HOUR
, DAY
, WEEK
,MONTH
, QUARTER
, or YEAR
.
The unit
value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_
. For example, DAY
and SQL_TSI_DAY
both are legal.
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02'); -> '2003-01-02 00:01:00' mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02'); -> '2003-01-09'
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 legal values for unit
are the same as those listed in the description of the TIMESTAMPADD()
function.
PS:返回后者-前者的值,当传入的参数是date类型时,在系统中它会被当做datetime类型处理,时间为00:00:00.
第一个参数的类型和TIMESTAMPADD相同:MICROSECOND
(microseconds), SECOND
, MINUTE
, HOUR
, DAY
, WEEK
,MONTH
, QUARTER
, or YEAR
.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); -> 128885
Note
The order of the date or datetime arguments for this function is the opposite of that used with theTIMESTAMP()
function when invoked with 2 arguments.
invoke:vt, 调用,祈求,引起,恳求