Using Created and Last Updated Timestamps in MySQL 4.0 - Tutorial

If you are working with MySQL 4.0 and need to keep track of when records were created or last updated, you can use the TIMESTAMP data type. This data type automatically updates itself with the current date and time whenever the record is inserted or updated.

To create a table with a TIMESTAMP column, you can use the following syntax:

CREATE TABLE my_table (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

In this example, the "created_at" column will be set to the current timestamp when a new record is inserted, while the "updated_at" column will be updated with the current timestamp whenever the record is updated.

To retrieve the created and last updated timestamps, you can simply query the corresponding columns:

SELECT created_at, updated_at FROM my_table;

You can also use the UNIX_TIMESTAMP() function to convert the timestamps to Unix timestamps for easier manipulation:

SELECT UNIX_TIMESTAMP(created_at), UNIX_TIMESTAMP(updated_at) FROM my_table;

Using TIMESTAMP columns is a simple and effective way to keep track of record creation and updates in MySQL 4.0. Just be aware that the maximum value for a TIMESTAMP column is '2038-01-19 03:14:07', so if you need to store dates beyond that, you will need to use a DATETIME column instead.