datetime or timestamp? help please

[drewkennelly]

Hi,

I am trying to make a simple system to add news articles to my clubs website (www.paekakarikisurflifeguards.com).

Basically what I would like to happen is this:

The person responsibile for the news updates fills out the form with the required details - title, body and then when they click 'add' the article is saved to the database. At this point I would like the date and time to automatically be stored in the database as well (in a field called 'date' funnily enough 🙂 )

I am unsure whether the data type of the field 'date' should be 'datetime' or 'timestamp' and furthermore how exactly I would get the database to use the current date/time as the value for that field when the article is added.

I realise this may seem simple and Im sure there is an easy way to do it however I am not very experienced with mysql/php so any help would be greatly appreciated.

Thank you in advance,

Drew

[Newsgroup_User]

On 17 Mar 2007 in macromedia.dreamweaver.appdev, drewkennelly wrote:

> I am unsure whether the data type of the field 'date' should be
> 'datetime' or 'timestamp' and furthermore how exactly I would get
> the database to use the current date/time as the value for that
> field when the article is added.

You don't mention what DBMS you're using, but odds are that what you want
is 'timestamp'. That's the idea behind a timestamp field - it will take
whatever the time value is when the record is added/updated, without any
intervention from you.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[drewkennelly]

Hi,

It is a MySQL database.

[Newsgroup_User]

drewkennelly wrote:
> The person responsibile for the news updates fills out the form with the
> required details - title, body and then when they click 'add' the article is
> saved to the database. At this point I would like the date and time to
> automatically be stored in the database as well (in a field called 'date'
> funnily enough 🙂 )
>
> I am unsure whether the data type of the field 'date' should be 'datetime' or
> 'timestamp' and furthermore how exactly I would get the database to use the
> current date/time as the value for that field when the article is added.

What you want is a TIMESTAMP data type. As you'll see from the MySQL
reference manual, the first TIMESTAMP column in a table is automatically
updated when you enter NULL:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html

In effect, you don't need to supply any value, and a current timestamp
will be inserted.

Use the DATE_FORMAT() function in your SQL queries to extract the date
in the format you want:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

By the way, you shouldn't use the name of a data type as the name of a
column. You have been lucky with "date" because MySQL has made it an
exception to the rule (basically because so many people make the same
mistake as you). When choosing column names, try to use something that
indicates its meaning. Date is very vague, but "created", "written",
"updated", "arrival", "departure", etc are all good names for date
columns that convey more precisely what they're for.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[drewkennelly · Accepted solution]

Hi,

Thanks for that, I have it working now and changed the 'date' field to newsdate.

Thanks

Drew