Convert all colums in the rows from date to timestamp MySQL

Question

I have a database with 13000 rows. Each row contains the column 'date' which is now in this format (example): 2012-09-01 17:53:28, but I want them to be a TIMESTAMP.

What query should I run to update all columns named 'date' from date to timestamp on all rows?

Thanks

If the dates are stored in the same fashion as you state, i.e. **`2012-09-01 17:53:28`**, then you can simply use `ALTER TABLE \`tbl\` CHANGE COLUMN \`date\` \`date\` TIMESTAMP`. — hjpotter92, Sep 13 '12 at 14:42

score 6 · Answer 1 · answered Sep 13 '12 at 14:38

It's pretty straightforward to convert from one type to another using the ALTER TABLE command:

ALTER TABLE table_name CHANGE old_date new_timestamp TIMESTAMP

This process may take a while to complete if you have a lot of data and a large number of indexes.

I'm not sure why you'd want to switch these to the TIMESTAMP type as that has a much more limited range than DATETIME. The differences are documented and important to know.

score 3 · Answer 2 · answered Sep 13 '12 at 14:36

It's unclear what you are asking for. There exist following datatypes: date, time, datetime and timestamp. And there's UNIX_TIMESTAMP, which is the seconds since 1970-01-01, but I don't think that's what you're after.

I suggest you read this.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

When you want to convert your column date do:

ALTER TABLE yourTable MODIFY COLUMN yourDateColumn TIMESTAMP;

score 1 · Answer 3 · answered Sep 13 '12 at 14:25

1

SELECT UNIX_TIMESTAMP('2012-09-01 17:53:28');

answered Sep 13 '12 at 14:25

AnandPhadke

13,160
5
26
33

Nelson · Answer 4 · 2012-09-13T14:50:53.380

1

You can just use the UNIX_TIMESTAMP() function to return values as timestamp when doing a query. Like so:

mysqli_query("select UNIX_TIMESTAMP(date) as date from table");

edited Sep 13 '12 at 14:50

answered Sep 13 '12 at 14:29

Nelson

49,283
8
68
81

Why does this answer have `mysql_query` in it? – tadman Sep 13 '12 at 14:36
OP seems to be using mysql because have it on the title and one of its tags, just guessing he is using that function. – Nelson Sep 13 '12 at 14:40
Well, `mysqli` is admittedly an improvement, but the original question doesn't have anything to do with PHP. – tadman Sep 13 '12 at 15:20

Convert all colums in the rows from date to timestamp MySQL

4 Answers4

Linked