How to replace UNIX timestamp with mysql timestamp

CGRRay · #1 24-Jan-2008, 20:02

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?

davekw7x · #2 28-Jan-2008, 18:46

Quote:

Originally Posted by 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?

So: Use a php loop that calls mysql_query() to go through the rows of the first table. For each row, use mysql_query() to insert column 0 and column 1 and the newly formatted date into the second table. In the following example, i just used FROM_UNIXTIME in its default format. You could use your function (or look at the formatting options of FROM_UNIXTIME, if you prefer to stick with something standard).

PHP Code:


<?php

    $link = mysql_connect() or die("Could not connect: ".mysql_error());
    print "Connected to mysql.<br>";

    $db_database = "test1";
    mysql_select_db($db_database) or die("select: ". mysql_error());
    print "Selected data base $db_database<p>";
    $query = "SELECT * FROM files";
    $line = mysql_query($query) OR die("Could not query: ".mysql_error());
    while ($result_row = mysql_fetch_row(($line))) {
        print "result_row[2] : $result_row[2]<br>";
        $unixdate=$result_row[2];
        $sqld=mysql_query("SELECT (FROM_UNIXTIME($unixdate))");
        $myDate = mysql_result($sqld,0);
        print "myDate=$myDate<p>";
        // use mysql_query to insert 
        // result_row[0], result_row[1], and the newly
        // formated $myDate into the new table
    }

?>

The output was something like the following for a little data base with some file information (name, owner, timestamp). (I'm kind of new at this, so I used some extra intermediate variables so that i can print out debugging results.) I didn't actually make a new table (I wanted to save some of the fun for later); I just wanted to see how to go through the rows of the first table.

Code:

Connected to mysql.
Selected data base test1

result_row[2] : 1201538613
myDate=2008-01-28 08:43:33

result_row[2] : 1201556361
myDate=2008-01-28 13:39:21

result_row[2] : 1201539207
myDate=2008-01-28 08:53:27

result_row[2] : 1201374596
myDate=2008-01-26 11:09:56

Regards,

Dave

CGRRay · #3 29-Jan-2008, 09:35

Thanks Dave, I really appreciate your response. I also found a way to do it with a query that I ran against the table I wanted to convert from. Here's what I used:

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;

davekw7x · #4 29-Jan-2008, 09:56

Quote:

Originally Posted by CGRRay

I also found a way...

As I mentioned, I used a number of intermediate variables (and, therefore, extra statements) so that I could print things out for debugging (and pedagogical) reasons. I wasn't sure how far you wanted to "optimize" it.

Boiling it down to one elegant statement is something that I (usually) leave to others (power users) so that they can make their results meet their needs.

I appreciate seeing your approach.

Regards,

Dave

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
combining localtime() members into a single long long	Howard_L	C Programming Language	10	11-May-2007 22:58
Moving data from MySQL to MySQL	meurer	MySQL / PHP Forum	0	25-Jan-2007 09:03
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live	Web Hosting Forum	1	01-Sep-2006 02:54
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54

How to replace UNIX timestamp with mysql timestamp

Re: How to replace UNIX timestamp with mysql timestamp

Re: How to replace UNIX timestamp with mysql timestamp

Re: How to replace UNIX timestamp with mysql timestamp