[ericrodgers123]

Hello all,

I am new to PHP and just trying to learn a few things about it. I have some information in a MySql database that I have created. I am able to select the date, return the results, etc. But for some reason the timestamp is way off. I am not sure what I am missing. If you convert 1303564820015 I should get Sat, 23 Apr 2011 13:20:20 UTC but instead my year is 1903? I am sure I am missing something simple, like subtract 1970 from this date or add it, but right now it is kicking my butt.

Here is my code if some could take a look, and let me know where my mistake is I would appreciate it.

   <?php
		$con = mysql_connect("localhost","root","");
			if (!$con)
			  {
			  die('Could not connect: ' . mysql_error());
			  }
			
			  mysql_select_db("niagara", $con);
				
			  $result = mysql_query("SELECT * FROM dashboard_dischargeairtemp");

			  while($row = mysql_fetch_array($result))
  				{
  					echo date ('m/d/Y h:i:s a', $row['TIMESTAMP']) . "  " . "  ";
  					echo "<br />";
  					echo "   ". "  ". round ($row['VALUE'], 2);
  					echo "<br />";
  				}

			  
			  
    
	?>

[Dormilich]

date() uses a timestamp of seconds, but your timestamp uses milliseconds (a 32 bit timestamp has at most 10 numbers). you would have to discard the last 3 numbers.

alternatively, there are SQL functions for date formatting (DATE_FORMAT()), so you could do that on the DB side as well.

This post has been edited by Dormilich: 24 April 2011 - 01:01 PM

[ericrodgers123]

Dormilich, on 24 April 2011 - 12:58 PM, said:

date() uses a timestamp of seconds, but your timestamp uses milliseconds (a 32 bit timestamp has at most 10 numbers). you would have to discard the last 3 numbers.

alternatively, there are SQL functions for date formatting (DATE_FORMAT()), so you could do that on the DB side as well.

Thanks for the info and the fast response, is there a milliseconds function for PHP?

This post has been edited by Dormilich: 24 April 2011 - 01:10 PM

[Dormilich]

there is, but that won’t help you with your problem. all you need is strip off 3 characters of a string.

If you ask me, do the date formatting on the database.

[ericrodgers123]

Never mind I just thought about this I could just divide by 1000 and I think that should get me there. Correct?

[JackOfAllTrades]

That should do it, but I agree with Dormilich that you should let SQL handle the date formatting for you if at all possible.

[Dormilich]

ericrodgers123, on 24 April 2011 - 09:12 PM, said:

Never mind I just thought about this I could just divide by 1000 and I think that should get me there. Correct?

partly. date() requires an int, not a float. still, doing it on the DB side is better.

[ericrodgers123]

Dormilich, on 24 April 2011 - 03:08 PM, said:

ericrodgers123, on 24 April 2011 - 09:12 PM, said:

Never mind I just thought about this I could just divide by 1000 and I think that should get me there. Correct?

partly. date() requires an int, not a float. still, doing it on the DB side is better.

Sorry not trying to be knob, just trying to teach myself something here. What is the benfit of having MySQl doing the date formatiing instead of doing it with PHP?

[Dormilich]

ease of use. and most of the times it’s faster, too. if you have defined the timestamp as a date field (i.e. date, datetime, timestamp), MySQL can easily operate on this date field without the need to convert the input (i.e. it knows whether they are seconds or milliseconds).