Timestamp

[Daguse]

I am look for a way to stop a timestamp collum from updateding all the time. I want it to only up date on mysql insert and not mysql update. let me know if any one knows how... Thank you!

[bobleny]

Maybe this isn’t what your looking for and in fact depending on your situation it might not even work. This is what I did. I got rid of the timestamp and made it tiny text. What I did then is used hidden input fields to add the date.

So when the data is entered into the table the current time and date was entered into the table. So then when you call it, it calls the time that was entered. Like this…

Html for input:
<input type="hidden" name="time" value="<?php echo date("n-j-y g:i A",time()+21600) ?>">

Table:
forumtest

Field name:
time

Type:
tinytxt

Entered like this:
$time=$_POST['time'];
$query = "INSERT INTO forumtest VALUES ('$name','$subject','$message','$time')";
mysql_query($query);

Then I called it:
<?php echo $time ?>

So it should be nothing new to you but it does work

[AndyB]

Am I missing something here? The only fields that change on UPDATE are those for which you SET values. Don't set your time/date field when you UPDATE the record.

[fenway]

[!--quoteo(post=375961:date=May 21 2006, 11:49 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ May 21 2006, 11:49 PM) [snapback]375961[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Am I missing something here? The only fields that change on UPDATE are those for which you SET values. Don't set your time/date field when you UPDATE the record.
[/quote]
Well, the first TIMESTAMP column is different, and can "auto-stamp".

The solution is never to use this very stupid column type. Use a DATETIME, have created & modified fields, and set them whenever you please -- created on INSERT, and modified on UPDATE. Done.

Maybe one day in a future MySQL will actually support proper default values on DATETIME columns, but not any time soon.

[Daguse]

so if I understand you all, I should just do as Bob Leny said. changes the collum to text or datetime and add in the info with a PHP date() function. Now if I do that will MYSQL dateformate() still work?

[fenway]

[!--quoteo(post=376097:date=May 22 2006, 02:26 PM:name=Daguse)--][div class=\'quotetop\']QUOTE(Daguse @ May 22 2006, 02:26 PM) [snapback]376097[/snapback][/div][div class=\'quotemain\'][!--quotec--]
so if I understand you all, I should just do as Bob Leny said. changes the collum to text or datetime and add in the info with a PHP date() function. Now if I do that will MYSQL dateformate() still work?
[/quote]
No, you should do this in MySQL only:

[code]$query = "INSERT INTO forumtest ( name, subject, message, last_changed ) VALUES ('$name','$subject','$message',NOW())";[/code]

Don't bother with hidden fields -- it's insecure anyway. As I said earlier, you may want 2 DATETIME columns, one for created_on, and another for modified_on, but it's your call.

[Daguse]

[!--quoteo(post=376160:date=May 22 2006, 05:46 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 22 2006, 05:46 PM) [snapback]376160[/snapback][/div][div class=\'quotemain\'][!--quotec--]
No, you should do this in MySQL only:

[code]$query = "INSERT INTO forumtest ( name, subject, message, last_changed ) VALUES ('$name','$subject','$message',NOW())";[/code]

Don't bother with hidden fields -- it's insecure anyway. As I said earlier, you may want 2 DATETIME columns, one for created_on, and another for modified_on, but it's your call.
[/quote]


Ok thank you, now will I still be able to use dateformat() in mysql to pull the date and time in a format?

[fenway]

I guess... but why not use MySQL's DATE_FORMAT() function instead? No need to fight with different date formats at all.

[joepetrini]

If your using mysql 5 you can use a trigger to get fine grain control of record updates. The following will ensure that a timestamp field will not be changed on every update. You could of course use mysql date format in there as well.

[code]
CREATE TRIGGER test_trg BEFORE UPDATE ON sometable
FOR EACH ROW set new.created_at = old.created_at;
[/code]

Full example:
[a href=\"http://www.codedumpster.com/sql/mysql5_timestamps\" target=\"_blank\"]http://www.codedumpster.com/sql/mysql5_timestamps[/a]

[Wildbug]

[!--quoteo(post=375947:date=May 21 2006, 10:43 PM:name=Daguse)--][div class=\'quotetop\']QUOTE(Daguse @ May 21 2006, 10:43 PM) [snapback]375947[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I am look for a way to stop a timestamp collum from updateding all the time. I want it to only up date on mysql insert and not mysql update. let me know if any one knows how... Thank you!
[/quote]

Simply set the TIMESTAMP column equal to itself in the UPDATE. I.e., "UPDATE some_table SET ts_column=ts_column, another_column=...., etc."

No hidden fields, triggers, or other complications; works in older versions of MySQL, too.

FWIW, TIMESTAMP takes 4 bytes to store, DATETIME takes 8 bytes.

[fenway]

[!--quoteo(post=384723:date=Jun 16 2006, 03:15 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 16 2006, 03:15 PM) [snapback]384723[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Simply set the TIMESTAMP column equal to itself in the UPDATE. I.e., "UPDATE some_table SET ts_column=ts_column, another_column=...., etc."

No hidden fields, triggers, or other complications; works in older versions of MySQL, too.

FWIW, TIMESTAMP takes 4 bytes to store, DATETIME takes 8 bytes.
[/quote]
I agree that it's better to stay away from the "fancy" new MySQL 5 stuff... but TIMESTAMP fields are much less useful than DATETIMEs, particuarly because they're stored in a funny way and because they have a very silly date range.