Geek Answers Handbook

UNIX_TIMESTAMP returns 0

I am trying to return a value using the unix_timestamp function, but this behaves strangely.

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 does it return a value of 0 for values ​​between 4000-7000?

I see that the answer is related to the current time, because the interval that gives 0 changes to the transit time. What could be the reason?

Thanks in advance,

+4
source share
2 answers

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

 UNIX_TIMESTAMP(NOW()) + 4000

or simply

 UNIX_TIMESTAMP() + 4000

Both of them 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 , for example, 20071215235026.000000 . If you add 4000 to this, you may or may not get something similar to the real date. As you can see, you will not add seconds or any other specific object. If you get the correct date, UNIX_TIMESTAMP will return a timestamp, otherwise it will return 0 .

+4
source

Using NOW() to test this way can lead to confusing results. So put it in a variable:

 SET @now=NOW();

Let's look at this:

 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

Ahh. What is it? This 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 will represent 2012-06-17 17:62:01 . This is not what the UNIX_TIMESTAMP() whine does.

If we do this,

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

we reach the acceptable time range ( 2012-06-17 18:12:01 ), which can be estimated again.

+2
source

More articles:


All Articles