March 21, 2009

How do I make my MySQL table automatically make a timestamp when new data is added?

mysql
Carbonbased Lifeform asked:


I am trying to create a MySQL table with three columns. Two colummns are for data and a third the time at which the data is inserted. When I create the table, how do I write out the command to tell MySQL to create a timestamp column?

Tags: , ,

Filed under Programming & Design by administrator

Permalink Print

Comments on How do I make my MySQL table automatically make a timestamp when new data is added?

March 24, 2009

Edwin M @ 3:15 am

in your SQL query, use the mysql function curdate()

example:

INSERT INTO table (column, date, column) VALUES (’value’, curdate(), ‘value’)

just "JR" @ 5:48 am

First, don’t use “timestamp”: that is UNIX time stamp: the number of seconds since January 1 1970 00:00:00 GMT

Set the column variable as “datetime” type with the default value 0000-00-00 00:00:00 (for a format YYYY:MM:DD HH:MM:SS), and call this column “datum” (don’t use “date”, “datetime” or “timestamp”!: reserved words)
Advantage of this format: easy sorting!

When inserting, use:
$datum = date ( “Y-m-d H:i:s”);
This will get current time and date, correctly formatted.
Your sql:
$sql = “insert into `tablename` (`field1`,`field2`,`datum`)
values (’ ” . $f1val . ” ‘ , ‘ ” . $f2val . ” ‘ , ‘ ” . $datum . ” ‘ ) “;

nore h @ 6:17 pm

Table Name:
Column 1:
Column 2:
Column 3: