simple mysql notionw() func Question

They have: 28 posts

Joined: May 2003

HI, how do you prevent a date (timestamp) column from updating when doing an update to that row. Everytime I perform an update to a row the NOW() function causes it to update to the current time..I don't want that to happen. Can anyone help....I'm sure there is a really easy solution to this.

thanks,
Dave

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

don't update that field?

They have: 28 posts

Joined: May 2003

I'm not updating that field..I'm updating another field in that row and it always changes the date field automatically...hasn't this ever happened to you.

Mark Hensler's picture

He has: 4,048 posts

Joined: Aug 2000

Can you show us the query your running?

They have: 28 posts

Joined: May 2003

I don't mean to say this as an insult, but I can't believe none of you haven't had this problem and you have no idea what i'm talking about...geez.

Ok...Lets start off with a simple example...lets say I have a table with 3 fields.
varchar(20) name
TIMESTAMP datet
TEXT message

Ok, lets say I create a row ->

INSERT INTO table (name,datet,message) VALUES ('idunno myname', NOW(), 'this is a long message');
'

NOTE: that I used the NOW() function which pushes the currentdate and time in whatever form the date datatype i'm using. In this case its a TIMESTAMP(14), which is 4 bytes and is in the form of YYYYMMDDHHMMSS. Ok, now lets say I want to update that row to change the name or message.

Syntax would be something like this.

UPDATE table SET message='changed message' WHERE name='idunno myname'
'
Now this is where my problem is...Notice I didn't do anything to the date field, yet mysql automatically updates the date field. If I look at the row, the datet field will now be changed to when I last updated the row. I don't want this, I want the datet field to remain the same.

I hope this helps for those of you that are confused..

Thanks for any help in advance,
Dave

They have: 28 posts

Joined: May 2003

Well, i finally figured out a solution to this that never occured to me before...LOL

Why don't I just set the date equal to the previous value which works fine and as far as I know thats the only way to preven mysql from updating the date field.

Example, UPDATE table SET field1='field2' datefield=datefield;

horray.

mairving's picture

They have: 2,256 posts

Joined: Feb 2001

You could make the field type a int(11), then produce your own timestamp; $timestamp = time(); and use this value. According to the MySQL Documentation, the timestamp is working like it should

Quote:
The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
You explicitly set the TIMESTAMP column to NULL.

Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states

druagord's picture

He has: 335 posts

Joined: May 2003

I use the solution provided by section31 in many applications that i have writen and it work very well

mairving's picture

They have: 2,256 posts

Joined: Feb 2001

druagord wrote: I use the solution provided by section31 in many applications that i have writen and it work very well

Can you explain or link to section31?

Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states

druagord's picture

He has: 335 posts

Joined: May 2003

Sorry about this here the code he submited

Quote:
UPDATE table SET field1='field2' datefield=datefield;

like i said i often use this for table that have fields insertiondate modifydate

UPDATE table1 SET toto='ujdshgsjjsdh',insertiondate=insertiondate;
'
note that even if i don't update modifydate it will be automaticly since it is a timestamp field

IF , ELSE , WHILE isn't that what life is all about

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

So there's the thing, I guess, eh? No one has had this problem since we don't try to use timestamp for things that don't need it to be automatically updated. Wink

They have: 461 posts

Joined: Jul 2003

section31 wrote: HI, how do you prevent a date (timestamp) column from updating when doing an update to that row. Everytime I perform an update to a row the NOW() function causes it to update to the current time..I don't want that to happen. Can anyone help....I'm sure there is a really easy solution to this.

thanks,
Dave

timestamps are made to update whenever a non-select statemetn is run on the row. the only way to override that tis to either use a datetime feild which needs and explicit update, or to update that feild with the content that was previously there.

i read this earlier this week wehn mark linked me to the date & time functions... or was it suzanne... might have been both....in any case, for a more in depth explanation, it's on the mysql manual at....ok. i can't seem to find it int he threads right now... anyone have it handy?

you can always go to mysql.com and then search the manual for "datetime"

POSIX. because a stable os that doesn't have memory leaks and isn't buggy is always good.

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

http://www.mysql.com/doc/en/DATETIME.html

It was probably me. I feel like I link to that page weekly. Wink

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.