simple mysql notionw() func Question
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 posted this at 05:10 — 28th January 2004.
She has: 5,507 posts
Joined: Feb 2000
don't update that field?
section31 posted this at 05:11 — 28th January 2004.
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 posted this at 06:05 — 28th January 2004.
He has: 4,048 posts
Joined: Aug 2000
Can you show us the query your running?
section31 posted this at 19:11 — 28th January 2004.
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
section31 posted this at 20:03 — 28th January 2004.
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 posted this at 20:28 — 28th January 2004.
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
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
druagord posted this at 21:01 — 28th January 2004.
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 posted this at 22:10 — 28th January 2004.
They have: 2,256 posts
Joined: Feb 2001
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 posted this at 22:14 — 28th January 2004.
He has: 335 posts
Joined: May 2003
Sorry about this here the code he submited
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 posted this at 03:12 — 29th January 2004.
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.
m3rajk posted this at 06:07 — 29th January 2004.
They have: 461 posts
Joined: Jul 2003
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 posted this at 18:01 — 29th January 2004.
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.
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.