SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast Cabbage's Avatar
    Join Date
    Jun 2001
    Location
    New Jersey
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with timestamp in MySQL database

    I created a table that has info on updates to a section of my website. I want the date & time to be above the update text. Here is the SQL of the table

    CREATE TABLE songlist_updates (
    songlist_updates_id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
    songlist_updates_test text,
    songlist_updates_timestamp timestamp(14),
    PRIMARY KEY (songlist_updates_id)
    );

    The problem i am having is getting the timestamp in a usable form. All the functions in the manual i looked at dealt with manipulating the timestamp using the date() or time() functions when inserting the data but not extracting it from the database.

    Here is a row in the database
    INSERT INTO songlist_updates VALUES ( '4', 'Added info to Do Right.', '19990728120000');

    --What i want is for the top line to say
    Added on July 28, 1999 at 12:00

    --then the next line to say
    Added info to Do Right.

    So i can't figure out to use built in functions to get '19990728120000' into July 28, 1999 at 12:00

    Any help would be much appreciated!
    ~Cabbage
    http://www.jimmieschickenshack.net
    want to trade live recordings? JCS, Smashing Pumpkins, Local H, Toadies

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well what you want to do is use the built in MySQL functions to format your date column. Like this, replace the table and field names according to your settings.

    PHP Code:
     select DATE_FORMAT(timestampfield'%M %d, %Y at %l:%i') as timestampfield from tablename
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Enthusiast Cabbage's Avatar
    Join Date
    Jun 2001
    Location
    New Jersey
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used this as my select statement
    $updates = mysql_query("SELECT songlist_updates_test, DATE_FORMAT(songlist_updates_timestamp, '%M %d, %Y at %l:%i') as songlist_updates_timestamp FROM songlist_updates ORDER BY songlist_updates_timestamp");

    July 28, 1999 Wedt 12:00
    The date came out like above. I just have to tweek it a little an i'm sure it will be fine. Thanks so much!
    ~Cabbage
    http://www.jimmieschickenshack.net
    want to trade live recordings? JCS, Smashing Pumpkins, Local H, Toadies


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
  •