8 Replies - 1734 Views - Last Post: 25 April 2011 - 03:02 AM Rate Topic: -----

#1 ericrodgers123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-April 11

Help with Date From Epoch time to date time

Posted 24 April 2011 - 12:51 PM

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 />";
  				}

			  
			  
    
	?>




Is This A Good Question/Topic? 0
  • +

Replies To: Help with Date From Epoch time to date time

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,251
  • Joined: 08-June 10

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 12:58 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 ericrodgers123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-April 11

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 01:08 PM

View PostDormilich, 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

Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,251
  • Joined: 08-June 10

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 01:12 PM

there is, but that wont 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.
Was This Post Helpful? 1
  • +
  • -

#5 ericrodgers123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-April 11

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 01:12 PM

Never mind I just thought about this I could just divide by 1000 and I think that should get me there. Correct?
Was This Post Helpful? 0
  • +
  • -

#6 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6066
  • View blog
  • Posts: 23,526
  • Joined: 23-August 08

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 01:34 PM

That should do it, but I agree with Dormilich that you should let SQL handle the date formatting for you if at all possible.
Was This Post Helpful? 0
  • +
  • -

#7 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,251
  • Joined: 08-June 10

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 03:08 PM

View Postericrodgers123, 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.
Was This Post Helpful? 0
  • +
  • -

#8 ericrodgers123  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-April 11

Re: Help with Date From Epoch time to date time

Posted 24 April 2011 - 08:21 PM

View PostDormilich, on 24 April 2011 - 03:08 PM, said:

View Postericrodgers123, 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?
Was This Post Helpful? 0
  • +
  • -

#9 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3541
  • View blog
  • Posts: 10,251
  • Joined: 08-June 10

Re: Help with Date From Epoch time to date time

Posted 25 April 2011 - 03:02 AM

ease of use. and most of the times its 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).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1