Insert current date into field in MySQL

nscherneck

Hello,

I think this one may be simple, so here goes. I want a field in a table to log the date that each record was inserted and another field for the time it was inserted. Im sure this is possible.

The table is called "members" and the fields are "date_added" and "time_added". Thank you all....

devinemke

you should really use mySQL's TIMESTAMP type.

nscherneck

Excellent Devine, thank you. I did some reading on the TIMESTAMP feature and noticed that there are different type. These being:

TIMESTAMP(14) YYYYMMDDHHMMSS

TIMESTAMP(12) YYMMDDHHMMSS

TIMESTAMP(10) YYMMDDHHMM

TIMESTAMP(8) YYYYMMDD

TIMESTAMP(6) YYMMDD

TIMESTAMP(4) YYMM

TIMESTAMP(2) YY

This is great being able to format the time and date that gets inserted, but i cannot figure out where to define the type using PHPmyAdmin. Does anyone know?

devinemke

the type is TIMESTAMP and length is the number of characters you want to store (2, 4, 6, etc...).

be advised that there are majors changes with regard to TIMESTAMPS starting with mySQL 4.1 that you should consider when upgrading (if haven't already).

nscherneck

perfect, thank you. another thing, when inserting a new record into the database how do i automatically get the TIMESTAMP field to become updated?

devinemke

Originally posted by nscherneck
perfect, thank you. another thing, when inserting a new record into the database how do i automatically get the TIMESTAMP field to become updated?

nothing. that's the whole point, it is done automagically

nscherneck

Im a bit confused then. What ive got is a table with three columns: date_added, username, and email. Of course the date added is the TIMESTAMP field. Now im sending data to this data via a form, before i had added the date_added column my query looked like this:

$Query = "INSERT into $TableName values ('$Array[Username]', '$Array[Email]')";

Theres nothing i need to put in this INSERT command to take the place of the date_added column?

devinemke

$Query = "INSERT into $TableName SET 
username = '" . $Array['Username'] . "',
email = '" . $Array['Email'] . "'";

nscherneck

Splendid devine. This way your not required to fill each field in a row, correct?

devinemke

Originally posted by nscherneck
This way your not required to fill each field in a row, correct?

whatever fields you do not specify in your INSERT query will be filled in with whatever default values you chose at table creation time, which in the case of TIMESTAMP fields is the current date/time.

nscherneck

Excellent, thank you devine.