NOW()

[Kryptix]

UPDATE `users` SET `sub_expires` = NOW( ) + '2592000' WHERE `id` = '101'

 

This is returning 4294967295 when I expected it to return 128577889 (1259859889 + 2592000). I'm using Unix Time Stamp Generator as a test.

 

Is NOW() different or something?

 

I'm using this for a game where it checks to see if the user has subscribed. They purchase a subscription and it adds NOW() + '2592000' (30 days) to their 'sub_expires' column in their user entry. However, I'd like it to be able to detect the current entry and then add 30 days onto it, but what if the current entry is 60 days old? That'll just make it 30 days old... Right? I need it to basically check if their entry is => NOW() before adding '2592000' to it, otherwise just make reset the entry to NOW() + '2592000' (30 days). I hope that makes sense...

 

I'm using Java for this. I would be able to write some checks in PHP but I'm not so good with Java so is there anyway to do this in a query?

[premiso]

I am not 100% sure on this, but using the single quotes around the number you are adding could be making it take the string as a 1, or something different. Try this:

 

NOW( ) + 2592000

and see if that works, as since it is a number you should not need quotes around it.

[PFMaBiSmAd]

NOW()

 

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format

 

It's not a Unix Timestamp, so directly adding a numerical amount of seconds to it has no meaning.

 

What exactly are you trying to do?

 

[Kryptix]

NOW()

 

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format

 

It's not a Unix Timestamp, so directly adding a numerical amount of seconds to it has no meaning.

 

What exactly are you trying to do?

Go to Unix Time Stamp Generator and click the Now() button. It'll generate a Unix Time Stamp like 1259861291.

 

I need to generate that in a query. I thought NOW() was the way to go but obviously not.

[premiso]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

Check out "UNIX_TIMESTAMP", perhaps that is the method you are looking for?

[Kryptix]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

Check out "UNIX_TIMESTAMP", perhaps that is the method you are looking for?

 

Perfect! Thanks!

 

Now, is there a way to check to see if 'sub_expires' is => UNIX_TIMESTAMP()?

 

So basically...

 

if (sub_expires => UNIX_TIMESTAMP()) {
UPDATE `users` SET `sub_expires` = sub_expires + 2592000 WHERE `id` = '101'
} else {
UPDATE `users` SET `sub_expires` = UNIX_TIMESTAMP() + 2592000 WHERE `id` = '101'
}

 

Is there anyway to do that in a single query?

[premiso]

UPDATE `users` SET `sub_expires` = CASE WHEN `sub_expires` >= UNIX_TIMESTAMP() THEN `sub_expires` + 2592000 ELSE UNIX_TIMESTAMP() + 2592000 END WHERE `id` = 101

 

Unsure if that will work, but I think it should...

[Kryptix]

UPDATE `users` SET `sub_expires` = CASE WHEN `sub_expires` >= UNIX_TIMESTAMP() THEN `sub_expires` + 2592000 ELSE UNIX_TIMESTAMP() + 2592000 END WHERE `id` = 101

 

Unsure if that will work, but I think it should...

You are the absolute MAN! Thanks so much dude!