MySQL Bugs: #82070: UNIX_TIMESTAMP() returns decimal (NO fractional seconds part)

Bug #82070	UNIX_TIMESTAMP() returns decimal (NO fractional seconds part)
Submitted:	30 Jun 2016 17:14	Modified:	11 Oct 2018 7:51
Reporter:	Laule Veit	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.30, 5.6.31, 5.7.13	OS:	SUSE (Leap 42)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
UNIX_TIMESTAMP() returns a decimal value when the argument is concatenated of a date and a time field.
The expected behaviour would be to return an integer value, because a time field has no fractional seconds part.

Description in the manual:

"the return value is an integer if no argument is given or the argument does not include a fractional seconds part, or DECIMAL if an argument is given that includes a fractional seconds part"

How to repeat:
CREATE TABLE tmp (
   tdate date,
   ttime time
);
INSERT INTO tmp VALUES ('2016-07-06','09:00:00');

SELECT 
UNIX_TIMESTAMP(CONCAT(tdate,' ',ttime)),
CONCAT(tdate,' ',ttime),
UNIX_TIMESTAMP('2016-07-06 09:00:00')
FROM tmp\G

*************************** 1. row ***************************
UNIX_TIMESTAMP(CONCAT(tdate,' ',ttime)): 1467788400.000000
                CONCAT(tdate,' ',ttime): 2016-07-06 09:00:00
  UNIX_TIMESTAMP('2016-07-06 09:00:00'): 1467788400
1 row in set (0.00 sec)

Hello  Laule Veit,

Thank you for the report and test case.
Observed that 5.6.31/5.7.13 are affected.

Thanks,
Umesh

This is not a bug, see the description above.
If you really need this value as an integer, you might add a CAST operator, e.g:

  SELECT CAST(UNIX_TIMESTAMP(CONCAT(tdate,' ',ttime)) AS SIGNED);