Suzanne — Thu, 29 Jan 2004 18:01:16 +0000

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

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

m3rajk — Thu, 29 Jan 2004 06:07:49 +0000

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"

Suzanne — Thu, 29 Jan 2004 03:12:37 +0000

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.

druagord — Wed, 28 Jan 2004 22:14:26 +0000

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

mairving — Wed, 28 Jan 2004 22:10:50 +0000

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?

druagord — Wed, 28 Jan 2004 21:01:32 +0000

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

mairving — Wed, 28 Jan 2004 20:28:10 +0000

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.

section31 — Wed, 28 Jan 2004 20:03:51 +0000

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.

section31 — Wed, 28 Jan 2004 19:11:20 +0000

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

Mark Hensler — Wed, 28 Jan 2004 06:05:56 +0000

Can you show us the query your running?