Created and Updated Timestamps on SQL Databases #sql #mysql #postgresql

created_updated_mysql.sql

// from MySQL 5.6.5+
CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

// beware of the timezone you're storing the datetime at

created_updated_postgresql.sql

CREATE TABLE test (
  id SERIAL NOT NULL PRIMARY KEY,
  created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION set_updated()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_updated
BEFORE UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE set_updated();