auto date

[proto]

can you get mysql to auto insert a date into a field? i tried to use timestamp(8) but it didn't work.

[drawmack]

If you create a mysql table that has a single timestamp or datetime field, this field is marked as not null and this field is an index then it will be automatically updated on inserts but not on updates.

for example:
If I have the following database structure:

create table Updates (
  ID BIGINT NOT NULL auto_increment,
  When TIMESTAMP NOT NULL,
  What  VARCHAR(255),
  PRIMARY KEY(ID),
  INDEX When (When)
)

then in php I can do this

$sql = "INSERT INTO Usdates (What) VALUES (\"" . $_POST['What'] . "\")";
mysql_query($sql);

then my ID and When fields will be automatically updated. But remember that in mySQL a timestamp is not a unix time stamp it's a date string in the format of YYYYMMDDHHMM so you'll have to parse this on your own with somthing like this

function mts_2_dateTime($mts) {
  $date = array();
  $date['Year'] = substr($mts,0,4);
  $date['Month'] = substr($mts,4,2);
  $date['Day'] = substr($mts,6,2);
  $date['Hour'] = substr($mts,8,2);
  $date[Minute'] = substr($mts,10);
  return $date;
}