a really random problem with formatting dates...

[tedstonea]

Hi there,

I've got a peculiar problem with date formatting. I am trying to format a date retrieved from a mysql datestamp column, using the following:
date("d/m/y",$dateadded)
However, when you do this, the date goes to 19 Jan 2038. I've read somewhere that this is the highest date that Apache Server can go up to at this point in time. The date stored in the timestamp column is correct. What is going on???!!!

Cheers
-Andrew

[phpnovice]

The second argument of the date() function must be an integer timestamp value. I'm guessing that this is not what you are supplying. You are probably supplying an SQL Date value -- not a timestamp. There are ways to convert an SQL Date value to an integer timestamp value.

[NogDog]

Probably your best bet is to do the date formatting as part of the sql (http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html):

$query = "SELECT DATE_FORMAT(date_column, "%d/%m/%y") FROM table_name WHERE some_column = '$some_value'";
$result = mysql_query($query);

[ShrineDesigns]

store timestamps or data as INT(10) UNSIGNED, then date conversions are cake, ex. date("m/d/Y", $datefromdb);

[tedstonea]

Hi again,

I've tried Nogdog's above suggestion, but I can't get it to work: it keeps coming back saying that the variable I have put the result into is undefined. Any ideas?

Thanks a lot
-Andrew

[NogDog]

Hard to tell without seeing what code you used. Make sure you're replacing "date_column" and "table_name" with the applicable column and table names from your database, perhaps?

[tedstonea]

*bump*

Ok...now i need to get the code totally clear in my head! here goes: basically, I have a mysql database with a table in it that stores news items. When a news item gets added to the table, the standard form of timestamp in a mysql table kicks in automatically (TIMESTAMP 14 digits) to put the date etc in. This is the correct date. However, when I try to get it back out of the table, usingSELECT DATE_FORMAT(dateadded,'%d/%m/&y') WHERE column1=criteria
the PHP variable I then try to store it in decides that this value is unexistent. However just using SELECT dateadded WHERE column1=criteria does work. I'm really confused now!

-andrew

[abudabit]

I used to use the sql date features, but then after realizing it was better just to stay in PHP used formats, I started storing time stamps in INT columns instead. Works great! Like ShrineDesigns said, store in INT columns and you won't have these problems. Just use $var = time(); right before you store data and then just put the $var into your insert or update. Simple.

[NogDog]

Hmmm... timestamp types are a bit tricky at best, especially since they changed between MySQL 4.0 and 4.1. If you are using 4.1+, try this kludge:
DATE_FORMAT(timestamp_col + 0, "[format string]")

[tedstonea]

Just to say, I have eventually gone with the suggestion of using a PHP style timestamp. It seems to work well. Thanks for all your help everyone!
-Andrew