How UNIX_TIMESTAMP works

Question

I'm very desapointed :-(

Why this request works fine

select UNIX_TIMESTAMP(STR_TO_DATE("08:00:00","%H:%i:%s"));

and why this other one faile ?

SET @date = STR_TO_DATE("08:00:00","%H:%i:%s");
select @date as _date, UNIX_TIMESTAMP(@date) as _unixtimestamp;

mysql Ver 14.14 Distrib 5.5.60, for debian-linux-gnu (x86_64) using readline 6.3

GMB GMB · Accepted Answer · 2020-12-09T11:52:38

You are giving a string that has a time portion only to str_to_date(), so the database needs to choose which datatype it should return.

I suspect that the variable assignment yields a TIME, which is not a valid input for UNIX_TIMESTAMP(). This can be seen when looking at the result of your sedonc statement, where @date is in time format:

_date    | _unixtimestamp
:------- | -------------:
08:00:00 |              0

On the other hand, the direct conversion within the function (as in your first statement) probably gives enough context to MySQL so it understands you want a DATETIME (the date part defaults to the current date).

The problem goes away if you are explicit about the target datatype:

set @date = cast(str_to_date('08:00:00', '%H:%i:%s') as datetime);
select @date as _date, unix_timestamp(@date) as _unixtimestamp;

Yields:

_date               | _unixtimestamp
:------------------ | -------------:
2020-12-09 08:00:00 |     1607500800

Demo on DB Fiddle

How UNIX_TIMESTAMP works

1 Answers