Converting Timestamp problem

[atokatim]

I have been trying to figure this out, and finally noticed what is causing the problem. I don't know how to fix it though.

// mysql query picks up the timezone.  mine is 1

$newdate = date('n/j/y g:i a', strtotime($sql->timezone . ' hours', strtotime($fulldate)));

If the strtotime($fulldate) = 8/2/07 0:01 am, the date comes out looking like this :

12/31/69 5:59 pm

how do I fix that? Everything else is fine as long as the time is not 0:##

[Tim_Silva]

I had something similar happening to me not long ago and in my case it was related to mySQL timestamp that needed to be converted to UNIX. I solved with the function

function convert_timestamp ($timestamp)
{
    $parts = sscanf($timestamp, '%04u%02u%02u%02u%02u%02u');
    $string = vsprintf('%04u-%02u-%02u %02u:%02u:%02u', $parts);

return strtotime($string);
}

Tim

[NogDog]

Where does $fulldate come from? I suspect you'd have better luck using it in strtotime() if it were in Y2K-compliant yyyy/mm/dd or yyyy-mm-dd format (8/2/07 is very ambiguous as to which value is month, day, or year).

[atokatim]

how would i make it y2k compliant?

[NogDog]

atokatim wrote:

how would i make it y2k compliant?

That depends on the source of the value. If it's coming from a database, it can be handled in the query that retrieves it. If it's coming from a form, then the best thing is to supply 3 select drop-downs, one each for month, day, and year; which would then be concatenated together into a yyyy/mm/dd format. This avoids confusion as opposed to depending on the user to type the entire string in the right sequence.

[atokatim]

it is coming from the database as a timestamp, then converted to a regular date and time along with changing the timezone according to each user.

Example :
my time zone is -6
$timezone would = 1 because of where my server is.

That works fine as long as the time does not equal 0:##

[NogDog]

If using MySQL:

// example query:
$query = "SELECT col1, col2, UNIX_TIMESTAMP(timestamp_column) AS unixtime FROM table_name WHERE id=123";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
   $newdate = date('n/j/y g:i a', strtotime($sql->timezone . ' hours', $row['unixtime']));
}

[atokatim]

ok. here is what I got :

$thread = mysql_fetch_object(mysql_query("SELECT lastmid, UNIX_TIMESTAMP(lastpost) AS last_post FROM forum_threads WHERE tid='$topic->id' ORDER BY lastpost DESC LIMIT 1"));

echo $thread->last_post;

// returns 0 ???

Why is it returning 0?

[NogDog]

I would separate out your query and make sure it was successful before fetching anything from it. Also, I'm not sure how well an object member is handled in a quoted string, so I'd try the following:

$query = "SELECT lastmid, UNIX_TIMESTAMP(lastpost) AS last_post FROM forum_threads WHERE tid='".$topic->id."' ORDER BY lastpost DESC LIMIT 1";
$result = mysql_query($query) or die("Query failed ($query): " . mysql_error());
$thread = mysql_fetch_object($result);
echo $thread->last_post;

[atokatim]

It was successful because it returns the lastmid. I checked the database to ensure that the lastpost row was not empty too.