How to add 1 year to timestamp

canton

How can i add 1 year to a timestamp within Table.Field

below is my very wrong code but you can get were i coming from.

Update table SET field DATE_ADD(post_date, INTERVAL 1 YEAR);

please advise a more efficient and correct way of doing

thanks in advance

bradgrafelman

You don't have an '=' assignment operator in there.

Also note that you can get rid of the DATE_ADD() command and simply do:

post_date + INTERVAL 1 YEAR

instead.

canton

i tried

Update table SET post_date + INTERVAL 1 YEAR;

but recived an error

UPDATE table SET post_date + INTERVAL 1 YEAR

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ INTERVAL 1 YEAR' at line 1

what have i done wrong?

thanks again

bradgrafelman

Again, you're missing a column and the '=' assignment operator. You've got to set a column equal to the new value.

canton

thanks for that it worked this time but it nulled the column

so you know the column properties are

Field: post_date
Type: int(10)
Null: Yes
Default: NULL

the column was already populated with timestamp data

so what do you think may have gone wrong?

your help is very much appreciated 😃

canton

so you know i backed up the table and now has been reverted.

any ideas on the problem?

bradgrafelman

canton wrote:
the column was already populated with timestamp data

If by "time stamp" data you mean "a Unix timestamp stored in a very unusuable format," then I guess that's true. 🙂

You should convert the 'post_date' column into a DATETIME column (since that's what it holds - a date and a time) so that it's easier to work with it.

canton

i really don't want to change the properties of the column if i can get way with it, as im not sure if it will break the software being used which is ibp 3.0 forum.

Is there not away around it without changing to DATETIME . If not if i change to DATETIME do you think the software will still function as normal or...?

thanks again

bradgrafelman

canton wrote:
do you think the software will still function as normal or...?

No, since it'll expect to get nothing but an integer back from the DB.

If you have to leave it as an integer, then you're going to have to use FROM_UNIXTIME() to convert the integer into the proper date format, and then UNIX_TIMESTAMP() to convert the newly calculated date value back into an integer for storage.

canton

Please could you help by creating the full query/s needed to give me the desired effect.

canton

Could someone please help as im trying to recover from someone hacking my site and i need to have it running as soon as possable.

Thank in advance