Should I use a timestamp field in mysql for timezones or other?

Question

MY previous site used DATETIME fileds in MySQL to store all dates/times. On my new site I am adding in timezone capability so I am wanting to store all submitted times at UTC timestamp, I am a little confused now though after reading another post on SO, I read that the timestamp is updated everytime you update the record, if that is the case then should I be storing my UTC timestamp into a text filed or what?

I was planning to store the UTC value with this below, am I wrong, please help

$now = gmdate('U', time());

Answer 1

ADD COLUMN name TIMESTAMP DEFAULT 0 is your friend. Without DEFAULT some_constant_value, MySQL will automatically update the column every time the row is updated (ref. MySQL 5.0 Reference Manual :: ... 10.3.1.1 TIMESTAMP Properties)

Answer 2

I read that the timestamp is updated everytime you update the record

Just some info on timestamps:

• For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for 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.

• Any single TIMESTAMP column in a table can be used as the one that is initialized to the current date and time, or updated automatically. This need not be the first TIMESTAMP column.

• If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP or a constant date and time value.

• To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then, for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs.