Jump to content


Photo

Timestamp


  • Please log in to reply
10 replies to this topic

#1 Daguse

Daguse
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 22 May 2006 - 02:43 AM

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!

#2 bobleny

bobleny
  • Members
  • PipPipPip
  • Advanced Member
  • 429 posts

Posted 22 May 2006 - 03:14 AM

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
-- www.firemelt.net --
First do me a favor and read this: JavaScript is NOT Java - Then read this: www.php.net - When your done with that, read this Topic
After that, floors open. I and anyone else will be MORE than happy to answer YOUR query! [Topic Solved]
Cheer up, the worst has yet to come...

#3 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 22 May 2006 - 03:49 AM

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.
Legend has it that reading the manual never killed anyone.
My site

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 May 2006 - 06:17 AM

[!--quoteo(post=375961:date=May 21 2006, 11:49 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ May 21 2006, 11:49 PM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Daguse

Daguse
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 22 May 2006 - 06:26 PM

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?

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 May 2006 - 09:46 PM

[!--quoteo(post=376097:date=May 22 2006, 02:26 PM:name=Daguse)--][div class=\'quotetop\']QUOTE(Daguse @ May 22 2006, 02:26 PM) View Post[/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:

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

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 Daguse

Daguse
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 22 May 2006 - 10:41 PM

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

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

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?

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 May 2006 - 05:19 AM

I guess... but why not use MySQL's DATE_FORMAT() function instead? No need to fight with different date formats at all.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 joepetrini

joepetrini
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 16 June 2006 - 03:21 PM

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.

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

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

#10 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 16 June 2006 - 07:15 PM

[!--quoteo(post=375947:date=May 21 2006, 10:43 PM:name=Daguse)--][div class=\'quotetop\']QUOTE(Daguse @ May 21 2006, 10:43 PM) View Post[/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.
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 June 2006 - 08:39 PM

[!--quoteo(post=384723:date=Jun 16 2006, 03:15 PM:name=Wildbug)--][div class=\'quotetop\']QUOTE(Wildbug @ Jun 16 2006, 03:15 PM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users