MySQL timestamp data type

Richard Korts
Richard Korts used Ask the Experts™
on
Based on what I've read of this, I'm guessing that if a timestamp is included as a field in a table & a record is inserted into that table, the value of the field is set to the then current date & time (server time) to the nearest second. I'm guessing that you DO NOT include any "value" for the timestamp field.

Of course I'm assuming I can report that value like a datetime.

Is this correct?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
slinkygnPresident

Commented:
Depends on how you defined the field - but, by default, yes.  A single TIMESTAMP field functions, by default, as a "modified date/time" field if you don't give it a value.

You can change that functionality a number of ways, though:
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
slinkygnPresident

Commented:
Sorry, missed the last bit there: yes, it functions basically as a DATETIME.  Only key difference is that it'll convert from the defined local timezone to UTC for storage, and then back to the local timezone for retrieval.
Richard KortsBusiness Owner / Chief Developer

Author

Commented:
To slinkygn

So it will also modify the timestamp if I update the row?

I don't want that.
Top Expert 2012
Commented:
If you specify DEFAULT CURRENT_TIMESTAMP in the column definition it will assign the current time during insert but leave it alone during update.   See below:

mysql> CREATE TABLE t
    -> (
    ->   ts1 TIMESTAMP NULL DEFAULT NULL,
    ->   ts2 TIMESTAMP NULL DEFAULT 0,
    ->   ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t (ts1) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+---------------------+---------------------+
| ts1  | ts2                 | ts3                 |
+------+---------------------+---------------------+
| NULL | 0000-00-00 00:00:00 | 2012-05-22 09:49:40 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update t set ts1=now();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+---------------------+---------------------+---------------------+
| ts1                 | ts2                 | ts3                 |
+---------------------+---------------------+---------------------+
| 2012-05-22 09:49:56 | 0000-00-00 00:00:00 | 2012-05-22 09:49:40 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

Open in new window

slinkygnPresident

Commented:
As was stated in the link I included.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial