CODEWITHSUNDEEP
mysql
Barış Velioğlu
Barış Velioğlu

Reputation: 5817

UNIX_TIMESTAMP returns 0

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,

Upvotes: 5

Views: 2853

Answers (2)

glglgl
glglgl

Reputation: 91017

Using NOW() for testing in that way can lead to confusing results. So let's put it into a variable:

SET @now=NOW();

Let's have a look at it:

SELECT @now;
-> 2012-06-17 17:42:01

Hm. Maybe we want to use it in a numerical context?

SET @now=NOW()+0;
SELECT @now;
-> 20120617174201
SELECT UNIX_TIMESTAMP(@now);
-> 1339947721

Aah. What is that? It is the current date and time. 2012-06-17 17:42:01, and its UNIX timestamp.

And now?

SELECT @now + 2000;
-> 20120617176201
SELECT UNIX_TIMESTAMP(@now + 2000);
-> 0

According to the logic above, it would represent 2012-06-17 17:62:01. This is invalid, which makes UNIX_TIMESTAMP() whine.

If we do, however,

SELECT @now + 7000;
-> 20120617181201
SELECT UNIX_TIMESTAMP(@now + 7000);
-> 1339949521

we reach a range of valid time (2012-06-17 18:12:01) which can be evaluated again.

Upvotes: 2

Emil Vikström
Emil Vikström

Reputation: 91902

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.

Upvotes: 3

Related Questions