37

Is there some way mysql can store timestamp automatically in a record row whenever that it is created. I was trying to use timestamp(data type) with current_timestamp as default value but then realised this will get updated everytime the record is updated. I just need something that will store create timestamp.

Thanks

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Good Guy
  • 952
  • 2
  • 9
  • 7

6 Answers6

81

Set the DEFAULT constraint to use CURRENT_TIMESTAMP:

CREATE TABLE ...
  your_date_column DATETIME DEFAULT CURRENT_TIMESTAMP
  ...

For an existing table, use the ALTER TABLE statement:

ALTER TABLE your_table
ALTER COLUMN date_column SET DEFAULT CURRENT_TIMESTAMP

Unless you specify a value to for the date_column, the default will be the date & time the INSERT statement was run. NULL and DEFAULT or valid values to use the default constraint otherwise, assuming the column is nullable.

Matt
  • 5,315
  • 1
  • 30
  • 57
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    THanks OMG, but this is what i tried. Wouldn't this timestamp update everytime the record is updated? – Good Guy Feb 02 '11 at 03:46
  • 11
    @gaurav: No. If you specify the additional `ON UPDATE` clause, then the column will be updated on an UPDATE -- otherwise, it will be left alone... assuming you don't specify a different value in an UPDATE statement against the column. – OMG Ponies Feb 02 '11 at 03:47
  • 4
    Note: CURRENT_TIMESTAMP only works with DATETIME columns as of version 5.6. Otherwise, you will have to use a column of type TIMESTAMP instead. – Halogen May 30 '16 at 08:32
  • If the above doesn't work in RDS try this... `ALTER TABLE db_name.table_name` `ADD COLUMN datetime DATETIME DEFAULT CURRENT_TIMESTAMP` – user3188040 Nov 13 '17 at 20:20
  • I'm surprised none of the comments here point out that DATETIME does not work properly with timezones and storing "CURRENT_TIMESTAMP" in a datetime is fundamentally flawed as you cannot control its relation to a particular timezone. – Anther Sep 10 '19 at 16:43
3

You can get the full details on timestamps in MySQL at https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html.

The point that you care about is that if you define a timestamp column as DEFAULT CURRENT_TIMESTAMP clause and don't have an ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

But be warned. The obvious thing to want to do is to have two timestamp columns, one being the creation time and the other being the last update time. Unfortunately it is a documented MySQL limitation that MySQL does not support this. I have no idea why MySQL has such an odd limitation - no other major database has problems with this common use case.

nhahtdh
  • 55,989
  • 15
  • 126
  • 162
btilly
  • 43,296
  • 3
  • 59
  • 88
  • This restriction was lifted with MySQL version 5.6.5 in April 2014 http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html – Jeremy Fishman Sep 19 '14 at 20:33
1

FYI = "Datetime" is date and time fixed. "Timestamp" is variable date and time-- system time.

So, Have two columns. One Create Col, One Update Col.

Merlin
  • 24,552
  • 41
  • 131
  • 206
0

The following command will create a hello table
1. id integer
2. create_at with current time.

create table hello (id int, created_at datetime DEFAULT CURRENT_TIMESTAMP);
vidur punj
  • 5,019
  • 4
  • 46
  • 65
0
Create Table myTableName
     (
       userId int primary key
       userJoiningDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );

https://www.mysqltutorial.org/mysql-timestamp.aspx

Here is how you can create a column in which the time stamp is recorded when it is created. If you want to know How to update timeStamp each time that row is changed/updated, Check the above link.

Vijay
  • 1,163
  • 8
  • 22
-3

SELECT * FROM test WHERE timestamp >= CURDATE() AND timestamp < CURDATE() + INTERVAL 1 DAY ORDER BY timestamp;

Rakesh Dongarwar
  • 475
  • 7
  • 10