SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Bedford, UK
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Timestamp column - How NOT to update

    Hi All,

    I have a timestamp column in my database which is initially populated when adding a record through the web page.

    However, once this is populated I do not want it to change unless I specifically specify that it should. Yesterday I was doing some work through phpMyAdmin and I happened to do some updates on this table and found today that it had updated the timestamp column.

    How can I avoid this, change it.

    The web page basically records work carried out on a server, so creating an audit log. Obvioulsy the data and time are essential to this. There will be no updates on the table in the long-term, but until we have settled it in I don't want the timestamp changing.

    Any Ideas?

    Thanks

  2. #2
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    You can change the column type of the field to datetime instead, and use NOW() when inserting the row. This will avoid the timestamp's auto-updating behaviour.

    e.g. if your table looks like this:
    Code:
    CREATE TABLE mytable (
       `id` int unsigned not null auto_increment,
       `stamp` datetime not null,
    
        PRIMARY KEY (`id`)
    );
    You would use the following insert statement to set the initial value of 'stamp' to the current time.

    Code:
    INSERT INTO mytable (stamp) VALUES (NOW());
    Hope this helps

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The nature of timestamp type is that it updates automatically. If you don't need this to happen, just use another column type (for example DATETIME) and update it explicitly:

    INSERT INTO xyz (...., 'date_created') VALUES (....., NOW())

    Edit:

    Second...

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Bedford, UK
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks guys.

    Really appreciate that. I have tested and it works. Silly me for not chekcing this before hand!

  5. #5
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this MySQL? By default the first timestamp column will be updated automatically when an update occurs. The only way I know to stop that is to set the column to its current value as part of the update. i.e. UPDATE TABLE tablename SET somecolumn = 'somevalue',... etc. ... timestampcolumn = timestampcolumn WHERE ...

    Hope this helps.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •