UNIX_TIMESTAMP returns 0

Question

I am trying to return the value by using unix_timestamp function but it behaves strange.

set @currentdate:= UNIX_TIMESTAMP(NOW() + 1000) /* 1339947588 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 2000) /* 1339948188 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 3000) /* 1339948788 */

set @currentdate:= UNIX_TIMESTAMP(NOW() + 4000) /* 0 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 5000) /* 0 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 6000) /* 0 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 7000) /* 0 */

set @currentdate:= UNIX_TIMESTAMP(NOW() + 8000) /* 1339949388 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 9000) /* 1339949988 */
set @currentdate:= UNIX_TIMESTAMP(NOW() + 10000) /* 1339950588 */

Why it returns the value of 0 for the values between 4000-7000 ?

What I see that the answer related by the current time because the interval, that gives the 0, changes by the time passing. What can be the reason ?

Thanks in advance,

Answer 1

This is not strange at all. You are adding some numbers to the result of NOW() before calling UNIX_TIMSETAMP() on the result. I think you want to call UNIX_TIMESTAMP before adding your values:

UNIX_TIMESTAMP(NOW()) + 4000

or just

UNIX_TIMESTAMP() + 4000

Both of these will add 4000 seconds to the current time. Another solution is to use the INTERVAL keyword to add time units to a date:

NOW() + INTERVAL 4000 SECOND

According to the manual, when NOW() + 4000 is evaluated, NOW() returns a number in this format: YYYYMMDDHHMMSS.uuuuuu, e.g., 20071215235026.000000. If you add 4000 to this you may or may not get something which resembles a real date. As you can see, you will not add seconds or any other defined entity. If you get a correct date UNIX_TIMESTAMP will return a timestamp, otherwise it will return 0.