timestamp to date is killing me

Anon

I have been fighting this far too long. I am obviously clueless.

I have a table with the column day type bigint. I have written into this column the timestamp for Sept.25,2000. This shows as 969854400 - the number of seconds since Jan. 1, 1970. All ok so far.

I have included a script that does nothing but print out this date. The problem is it gives me Dec.31,1969. Obviously just before Unix time started? Can someone either push me in the right direction to fix this .... or just push me over a cliff. Either way I am out of my misery.

<!DOCTYPE HTML PUBLIC "-//w3c//dtd html 4.0 transitional//en" "hmpro6.dtd">

<HEAD>

</HEAD>
<BODY >
<center>
<?php
$db = mysql_connect("localhost", "opencoun", "snjamd");
mysql_select_db("opencountry_com",$db);
$result = mysql_query("SELECT * FROM cheylinsports ORDER by day ASC",$db);
if ($myrow = mysql_fetch_array($result))
{

$day1=$myrow["day"];
$day2 = date ("M-d-Y", mktime (0,0,0,$day1));

do {
printf("$day2<BR>\n");
}
while ($myrow = mysql_fetch_array($result));
}
else
{
echo "Sorry, no records were found!";
}
?>
</center>
</BODY>

Thanks in advance.
stujo

Anon

Stujo,

The best way would be to change the column type to "date " rather than "bigint" this will allow much easier date manipulation.

Mike
www.digitalegg.net

Anon

After changing the column type to "date" here is some code I found which will change that to unix time which can then be formatted easily. I don't remember where I got it from but it works great.

Trevor DeVore
Blue Mango Multimedia

MYSQL2UNIX

- format a given mysql timestamp ( YYYYMMDDHHMM ) to

a unix timestamp ( seconds since 1/1/1970 )

- returned value is now in a form that can be used with

php's date() function.

function mysql2unix( $mysql_timestamp ) {
$year = substr( $mysql_timestamp, 0, 4 );
$month = substr( $mysql_timestamp, 4, 2 );
$day = substr( $mysql_timestamp, 6, 2 );
$hour = substr( $mysql_timestamp, 8, 2 );
$min = substr( $mysql_timestamp, 10, 2 );
$sec = substr( $mysql_timestamp, 12, 2 );

$unix_timestamp = mktime( $hour, $min, $sec, $month, $day, $year );

return $unix_timestamp;
}

Anon

Thanks for the suggestion. I just got it to work 🙂

I am too embarrassed to say what the problem was, just that it was soooo elementary. Syntax problem on my part.

To fix it I used:

$day1 = $myrow["day1"];
$day2 = date ("M-d-Y", $day1);

Thanks for the help. Now that I have learned something I will go back and set it up the right way 🙂

stujo - the rookie of the day

Anon

Are you just saving the current date into the database? If so, I'd recommend making the column a timestamp like someone else said in this thread.

Mysql will automatically update the column with the current date.

mysql> create table test (id int, today timestamp, var text);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (id,var) values (10,'text');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+-----------------+------+
| id | today | var |
+------+-----------------+------+
| 10 | 20000924050549 | text |
+------+-----------------+------+
1 row in set (0.00 sec)
mysql> select id, date_format(today,'%H:%i:%s') as time, date_format(today,'%e %M %Y') as date, var from test;
+------+----------+-------------------+------+
| id | time | date | var | +------+----------+-------------------+------+
| 10 | 05:05:49 | 24 September 2000| text +------+----------+-------------------+------+
1 row in set (0.00 sec)

You can have mysql do all of the date saving and formatting, and you just echo it out in PHP. Makes it easy....