[SOLVED] why are dates stored in my mysql DB in a weird way??

[jeger003]

im using a script i purchased and the dates are stored like this 1230696669 and this is some date in December of 08.

 

how do i make it display correctly cause i need to call the dates and display to the user.

 

maybe display it like Dec. 25 2008?

 

Any help is appreciated.

[.josh]

That format is a unix timestamp.  It's your date stored as the number of seconds since the beginning of time.

[PFMaBiSmAd]

And to format it any way you want in a query use the mysql FROM_UNIXTIME() function with the second format parameter to give you the format you want - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

[jeger003]

And to format it any way you want in a query use the mysql FROM_UNIXTIME() function with the second format parameter to give you the format you want - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

 

thank you guys for the help. I didnt know any of this.

 

PFMaBiSmAd thank you for the link. I went over the page and i cant really tell where or how i use it.

 

i mean here is what i want to display, how do i use it with something like this? after

$query = mysql_query("SELECT name, location, date FROM users_table");


//would this work?
$query = mysql_query("SELECT name, location, date FROM users_table");
FROM_UNIXTIME(date,YYYYMMDDHHMMSS.uuuuuu);

[448191]

//would this work?

 

Sure.

 

Ok, maybe not. I guess it depends.

[jeger003]

//would this work?

 

Sure.

 

Ok, maybe not. I guess it depends.

 

lol oh that definitely solved my problem.

[chronister]

That format is a unix timestamp.  It's your date stored as the number of seconds since the beginning of time.

 

Sorry CV for correcting you. You may have meant this but it is not since the beginning of time... it is seconds elapsed since January 1, 1970 00:00:00 (UNIX epoch) not including leap seconds. Which is why you may frequently see a screwed up date revert to 12/31/1969.

 

http://en.wikipedia.org/wiki/Unix_time

 

And since it it not mentioned yet, the PHP function date() will format the string for you as well. If your pulling it from MySQL, your better off letting the SQL Server do the work for you, but date works like this

 

<?php
   echo date('m/d/y H:i:s','1230696669 '); // prints  12/30/08 22:11:09
?>

 

Nate

[.josh]

Sorry CV for correcting you. You may have meant this but it is not since the beginning of time... it is seconds elapsed since January 1, 1970 00:00:00 (UNIX epoch) not including leap seconds. Which is why you may frequently see a screwed up date revert to 12/31/1969.

 

Buzz kill.

 

It was both a joke and a simplified explanation, as I figured saying all that to the OP would roughly translate to "It's your date stored as the number of seconds since blah blah blah analretentivewhywon'tthisguyshutup moreblah wtfdoesallthatmeananywayz lolzblahblah".

[Mchl]

That format is a unix timestamp.  It's your date stored as the number of seconds since the beginning of time.

 

Sorry CV for correcting you. You may have meant this but it is not since the beginning of time... it is seconds elapsed since January 1, 1970 00:00:00 (UNIX epoch)

 

Well... it IS beginning of time, isn't it?

[jeger003]

That format is a unix timestamp.  It's your date stored as the number of seconds since the beginning of time.

 

Sorry CV for correcting you. You may have meant this but it is not since the beginning of time... it is seconds elapsed since January 1, 1970 00:00:00 (UNIX epoch) not including leap seconds. Which is why you may frequently see a screwed up date revert to 12/31/1969.

 

http://en.wikipedia.org/wiki/Unix_time

 

And since it it not mentioned yet, the PHP function date() will format the string for you as well. If your pulling it from MySQL, your better off letting the SQL Server do the work for you, but date works like this

 

<?php
   echo date('m/d/y H:i:s','1230696669 '); // prints  12/30/08 22:11:09
?>

 

Nate

 

lol...you guys are great!

 

thanks for the help Nate. It works great!!

 

this is what i did if anyone needs it in the future:

$query = 'SELECT date FROM listings';

$check_query = mysql_query($query) or die(mysql_error());

$fetch_data = mysql_fetch_array($check_query);

$fetch_date = $fetch_data['date'];

echo date('m/d/y H:i:s', $fetch_date);

 

 

Thanks Again Everyone!!