time/date conversion problems

[bran]

Hi all. I'm having some issues with time/date conversions. I'm pulling the original info from the metadata of photos, for example 2007:12:15 20:53:37

 

I'm then using strtotime() to convert it to a timestamp and store it in the db. Later on, I use date() to pull it back out and display it in a more friendly format, like Dec 15th, 2007, 8:53 pm.

 

When I do this conversion in a test page, ie:

$time="2007:12:15 20:53:37";
echo ("time is ".$time."<br>");
$time=strtotime($time);
echo ("time is ".$time."<br>");
$time=date("D F j, Y, g:i a",$time);
echo ('time is '.$time);

then it works fine, I get back the same time that I put in, in a nicer format. When I try to do it on my page, though, after letting the timestamp ferment in the database for a bit, I'm getting back a time that is off by 2 hours later, 10:53pm.

 

I've picked through the code on both pages and can't see any reason why it wouldn't be the same as the test. Any ideas?

[PHP_PhREEEk]

When you pull the time from the database, it should be a unix timestamp (based on how you say it was stored). After assigning it a variable, say $row['timestamp'], you would go:

 

<?php
// we have $row['timestamp']
$time = date("D F j, Y, g:i a", $row['timestamp']);
echo 'Time is: ' . $time;

 

PhREEEk

[PFMaBiSmAd]

The date() function uses the timezone your server is in. You could set the default time zone that php uses, but a better way would be to avoid using the date() function.

 

I recommend storing the the date/time information in your database using a DATETIME data type (YYYY-MM-DD HH:MM:SS) and then use the mysql DATE_FORMAT() function in your SELECT query to output the date in the format you want.

 

This will avoid doing any conversion that is dependent on time zones and will result in the least amount of php code and the quickest execution.

[bran]

it's stored as TEXT in the database. I had originally intended for 'unknown' to be a possibility when metadata was not available.

[PHP_PhREEEk]

it's stored as TEXT in the database. I had originally intended for 'unknown' to be a possibility when metadata was not available.

 

Well I'm rather confused....

 

I'm then using strtotime() to convert it to a timestamp and store it in the db.

 

If you are doing this, then it is in Unix format. strtotime() converts a string representation of a timestamp to a unix timestamp.

 

PhREEEk

[bran]

The whole possibility of 'unknown' caused a whole host of problems and was discarded. I just mentioned it because of the formatting in the db.

 

The column is set to TEXt but the value stored there is a timestamp.

[PHP_PhREEEk]

Alter the field to be int(11), NOT NULL and you'll probably also want to index it if you will be using timestamps to do lookups (as most typically do...)

 

PhREEEk