Is there a sql command to change...

Client Joined: Jul 18, 2003 Posts: 977

the regdate to unixtime?

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

I don't believe so, at least that I know of. Here is a php function that will. You could then create an update statement if you wanted to replace it.

Code:

$user_regdate = 'Aug 27, 1970';


echo strtotime($user_regdate);

Posted: Mon Jan 03, 2005 8:09 am

Thank you. So if I know the unixtime for a certain date I could create an sql update statement? What would be the wording of the update statement? Thanks again...

Posted: Mon Jan 03, 2005 10:45 am

ALTER TABLE <the table> CHANGE <the field> <the field> INT something

Then:

UPDATE <the table> SET <the field>=<newtime> WHERE something=this

Posted: Wed Jan 05, 2005 3:47 pm

Raven wrote:

I don't believe so, at least that I know of. Here is a php function that will. You could then create an update statement if you wanted to replace it.

Code:

$user_regdate = 'Aug 27, 1970';


echo strtotime($user_regdate);

So this woks for each individual date but I would still have to put that in 33,000 + times... Maybe I could export the regdate into another format and automate the change and then import it back in as unix time? Sound possible?

Posted: Wed Jan 05, 2005 3:55 pm

No No. I just used the echo statement to verify that it worked. You would read the table in a loop and update each record as DJ stated. You could key in on the record_id.

Posted: Thu Jan 06, 2005 7:49 am

DJMaze wrote:

ALTER TABLE <the table> CHANGE <the field> <the field> INT something

Then:

UPDATE <the table> SET <the field>=<newtime> WHERE something=this

Thank you both for your help but my level is not so high that I can look at this and immediatle figure it out. Can you please explain it completely? Here is my actual problem inserted into your example DJ

Code:

ALTER TABLE phpbb_users CHANGE reg_date <why the second the field> INT <what is something?>

Then

Code:

UPDATE phpbb_users SET reg_date=<how do I determine new time?> WHERE <have no clue>

Thanks for your help!

[edit]What about something like this?

Code:

UPDATE nuke_users SET reg_date = unix_timestamp (str_to_date (concat (reg_date, ' 12:00:00'), '%b %d, %Y %T)) WHERE reg_date is not null

[/edit]