Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7
  1. #1
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Something tells me that there's a command I'm missing for this, but oh well...

    Is there a command to see what time and date an entry was added to a table? at my site, tweaky.org (HEAVILY UNDER CONSTRUCTION) I have a msg entry spot on the front page where people can leave basic msgs. Basically all it does is take their msg and slaps it into a table. I have recently gotten an odd msg and was wondering when it was posted, in order to figure out if it's one of the guys at work or not screwing around.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  2.  

  3. #2
    Senior Member Brak's Avatar
    Join Date
    Apr 2003
    Location
    San Francisco, CA
    Posts
    3,413
    Member #
    1217
    Liked
    2 times
    add a field called "dateposted" DATETIME field type.. if you make it not null, it'll automatically add in the current date/time, or you can use php's date() function (www.php.net), and then REFORMAT it for output via DATEFORMAT() (mysql function). Hope that helps...
    Kyle Neath: Rockstar extraordinare
    The blog | The poetry site | The Spore site

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Only comment in Braks statement is that you'd prefer to use MySQL's NOW() function to get the date (1. it's faster because it's executing everything at the database and 2. it's a more accurate time)

    If you're going to add the column to the schema you might want to first add it as NULL so that you can manually enter a date for the older entries (like 01/01/1900 0:00:00) so that you know that they were entered before the column was added to the schema.

    Here's the DDL for adding the column:
    Code:
    ALTER TABLE msgs
      ADD COLUMN date_posted DATETIME NULL 
        DEFAULT NOW();

  5. #4
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    You can also use UNIX_TIMESTAMP() which makes it much easier to add and subtract dates, but it goes funky for dates earlier than the epoch (1970).
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  6. #5
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Ok, so I can add the function of a D/T stamp, but it's not inherently kept track of, so I can't find out when that msg was posted. Oh well, thanks!
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  7. #6
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    Well, after you add the new column (timestamp or datetime), alter your query, which inserts the actual message into the database to put " NOW() " for the new column. There is no inherent way in mysql to keep track of when a row was added, that'll involve way too much bookkeeping, which will also impact the performace.
    There and Back Again :Ogre:

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    DEFAULT will automatically populate it into the database.

    Transaction Logs will contain the dates of all transactions performed on your database.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:02 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com