Dates and times

[Namadoor]

Hello,
In a MySQL database, I have a datetime column. The format for this column is YYYY-MM-DD HH:MM:SS.

If the correct formate for the time is not inserted, It will just appear as 0000-00-00 00:00:00. So to get the correct time from a PHP script, I use code like this:
$datetime = date("Y-m-d H:i:s");
Now that it is in the database, I want to get it out (I know how to do this) and change it into something like Aug 12 2002 2:34pm. I also want to be able to apply time zones to it.

Any suggestions on how to do this?

[jburfield]

The easiest and fastest way to get the date into a format you want is to SELECT it that way from the database:

SELECT date_format(date_field, "%b %e %Y %l:%i %p") FROM ....

That will give you the format you are looking for.

-- Jason

[the_Igel]

If you want to apply php date() to mysql columns, you should use mysql UNIX_TIMESTAMP function:

$r = mysql_query("select unix_timestamp(datetime) as unixtime....");
$row = mysql_fetch_array($r);
$datetime = $row['unixtime'];
$formattedtime = date([i]format string[/i], $datetime);

[Namadoor]

So then how could you add or subtract hours from the time so that it can be changed for timezones?

[the_Igel]

unix time is the number of seconds from 01/01/1970.
Thus you should add 3600 to unix time to add an hour.