Adding Time / Date to MySQL DB

[eskimoet]

Ahoy, I've been messing about with MySQL and adding text to a database then displaying it and its going OK, but I need help with showing what time the text was added..

My current MySQL table thing:

CREATE TABLE data (Title VARCHAR(50), Description VARCHAR(200), Genre VARCHAR(30), Uploaded DATE);

But all that does is add 0000-00-00, what am I doing wrong? I'd like the date to be shown as dd / mm / yy, if possible.

Again would it be possible to arrange them by when they were added?

Cheers

[RDM]

you want to use the NOW() function when inserting the date. Also, you can't reformat the data in MySQL. You can only do this when outputting the data with PHP or using the SELECT statement.

[eskimoet]

Yeah I thought that might be the case for arranging them.. Should I replace the "DATE" after Uploaded with NOW() or is that wrong entirely?

[sjlplat]

Yeah I thought that might be the case for arranging them.. Should I replace the "DATE" after Uploaded with NOW() or is that wrong entirely?

Yes, replace DATE with NOW()

[eskimoet]

I tried:

CREATE TABLE data ( Title VARCHAR(50), Genre VARCHAR(30), Description VARCHAR(200), Uploaded NOW()); but it just gives me this error:

Error

SQL query:

CREATE TABLE DATA (
Title VARCHAR( 50 ) ,
Genre VARCHAR( 30 ) ,
Description VARCHAR( 200 ) ,
UploadedNOW( )
)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW())' at line 1

Where am I messing up?

Also thanks to both of you for taking the time to reply :)

[pghcollectibles]

how about this:
CREATE TABLE data ( Title VARCHAR(50), Genre VARCHAR(30), Description VARCHAR(200), Uploaded DATETIME)

upon inserting or updating you get the current time using one of these (http://us3.php.net/manual/en/function.time.php)

[RDM]

You need to first create the table like pghcollectibles said, and then when you insert or update you use the NOW() function to inserted the current date.


CREATE TABLE data ( Title VARCHAR(50), Genre VARCHAR(30), Description VARCHAR(200), Uploaded DATETIME)


INSERT INTO data (Uploaded) VALUES (NOW())

[sjlplat]

Oops, I didn't notice you were creating a table. :eek:

DATETIME is the correct syntax when creating the table, and NOW() will be used when querying the table to insert data.

[DataMan]

Ahoy, I've been messing about with MySQL and adding text to a database then displaying it and its going OK, but I need help with showing what time the text was added..


Instead of a date type of field, use a timestamp. It'll provide the date and time (thus the timestamp name:) ) in one field.

DataMan

[r2b2]

How about


CREATE TABLE data ( Title VARCHAR(50), Genre VARCHAR(30), Description VARCHAR(200), Uploaded DATETIME DEFAULT NOW())

That way you don't have to worry about dealing with the current time in your insert statement? (Basically if Uploaded isn't specified in your insert, it will default to NOW())

[eskimoet]

Wow, its turned into a hive of activity in here, thanks everyone for your answers and help :D