[SOLVED] Replacing UNIX timestamp with MYSql timestamp

[CGRRay]

I inherited a database that has a table of registration dates in unix format, for example 1200590610

, I want to change these dates to mysql timestamp format and insert them into a new table. I have a PHP script that does the conversion

<?php

$intime = (1200590610);

$read_in = date("Y-m-d H:i:s", $intime);

?>

The original table (members) has 3 fields: fn, ln, reg_date. reg_date is in unix format. I want to select the record, convert the date and insert it into a new table (mem) with the same 3 fields.

Can you help?

[fenway]

So from DB to DB?

[CGRRay]

I'm using mysql 5.0.45 and php 5.1.4

Yes, both tables are in the same db.

[fenway]

How about an INSERT INTO... SELECT WHERE... using FROM_UNIXTIME?

[CGRRay]

Just wanted to post what I used so others might benefit.

Your suggestion worked. Here's the code:

 

INSERT INTO new date(member_id,user_id,member_number, fn, joined,renewed,expires)

SELECT member_id, user_id, member_number, fn, FROM_UNIXTIME(joined), FROM_UNIXTIME(renewed), FROM_UNIXTIME(expires) FROM old_date;

 

Thanks for the help. Ireally appreciate it and I learned a lot getting this to work.

[fenway]

Glad to hear it! Always appreciated when solutions are posted, too.