MySQL create time and update time timestamp
You can have two columns of type timestamp in one table.
The following works for MySQL 5.0
create table t
(
id integer,
created_at timestamp default current_timestamp,
updated_at timestamp
);
I think you are confusing this with SQL Server (where timestamp is not really a "time stamp" and there is indeed a limit on a single "timestamp" column)
Edit: But you will need a trigger to update the update_at column each time the row is changed.
MariaDB/MySql: Setting CURRENT_TIMESTAMP on CREATE and changing noting on UPDATE
you can create trigger for this
DELIMITER //
CREATE TRIGGER user_prefs_before_insert
BEFORE INSERT
ON user_prefs FOR EACH ROW
BEGIN
SET NEW.updated = new.created;
END; //
DELIMITER ;
then another trigger for update
DELIMITER //
CREATE TRIGGER user_prefs_before_update
BEFORE UPDATE
ON user_prefs FOR EACH ROW
BEGIN
SET NEW.updated = CURRENT_TIMESTAMP();
END; //
DELIMITER ;
When is a timestamp (auto) updated?
Give the command SHOW CREATE TABLE whatever
Then look at the table definition.
It probably has a line like this
logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
in it. DEFAULT CURRENT_TIMESTAMP
means that any INSERT
without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP
means that any update without an explicit timestamp results in an update to the current timestamp value.
You can control this default behavior when creating your table.
Or, if the timestamp column wasn't created correctly in the first place, you can change it.
ALTER TABLE whatevertable
CHANGE whatevercolumn
whatevercolumn TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable
without changing the timestamp, that is,
To prevent the column from updating when other columns change
then you need to issue this kind of update.
UPDATE whatevertable
SET something = 'newvalue',
whatevercolumn = whatevercolumn
WHERE someindex = 'indexvalue'
This works with TIMESTAMP
and DATETIME
columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMP
s) When you use TIMESTAMP
s, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.
MySQL CURRENT_TIMESTAMP on create and on update
Guess this is a old post but actually i guess mysql supports 2 TIMESTAMP in its recent editions mysql 5.6.25 thats what im using as of now.
MySQL timestamp fields - created/modified
Depending on the driver, the 0000-00-00 date might be treated as null, which isn't valid for your column definition. But if that wasn't the case, any date prior to 1970 isn't a valid timestamp either.
I'd try defining "created" as DATETIME instead of TIMESTAMP
alter table users modify `created` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00'
(That is the lowest date a DATETIME can take). As to why the modified column is not working, it's part of the same problem. Tables are supposed to have only one timestamp column, but if you put two or more, only the first one can have CURRENT_TIMESTAMP. Silly mysql limitations.
This restriction will be lifted in future version, but I guess yours hasn't that yet.
Update only the time stamp in a MySql table
UPDATE last_user_activity
SET time_stamp = NULL
WHERE token="40aed4d9-c9ac-471e-8d53-b2baa0d72523";
OR
UPDATE last_user_activity
SET time_stamp = NOW()
WHERE token="40aed4d9-c9ac-471e-8d53-b2baa0d72523";
Having both a Created and Last Updated timestamp columns in MySQL 4.0
From the MySQL 5.5 documentation:
One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.
Changes in MySQL 5.6.5:
Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.
Related Topics
Multiple Counts Within a Single SQL Query
Db2 - Returning the Top 5 of Each Category
Oledb Case When in Select Query
Multiple Left Joins on Multiple Tables in One Query
Should the Data Access Layer Contain Business Logic
SQL One to One Relationship VS. Single Table
Sqlite: Count Slow on Big Tables
How to Concatenate Values with Same Id in SQL
Oracle Insert into Two Tables in One Query
How to Use a SQL Window Function to Calculate a Percentage of an Aggregate
When Is a Good Situation to Use a Full Outer Join
Query Grants for a Table in Postgres
How to Do SQL Select Top N ... in As400
Combine Multiple Rows into Multiple Columns Dynamically in SQL Server
Managing Hierarchies in SQL: Mptt/Nested Sets VS Adjacency Lists VS Storing Paths