SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
-
Aug 11, 2001, 19:36 #1
- 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
-
Aug 11, 2001, 20:14 #2
- 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.
-
Aug 12, 2001, 08:25 #3
- 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