On my current site I use a mysql DATETIME field to store a date/time a item is posted, example a user post's a comment, or user table has date/time of when the user was created, those are just a couple of examples, I have tons of fields that need to use this.
I currently would store a mysql record with the DATETIME value being populated in mysql with this in my PHP code now() like this:
<?PHP
$sql = "INSERT INTO online_users
(user_id,
online_id,
ip,
datetime,
location,
gender)
values ('$cache->userid,$unid,$LOCALIP,NOW(),$location,$g)";
executeQuery($sql);
?>
Then when I show the mysql records, I just run some normal date formats to show it how I want.
Here is my situation now, I am building a new site, it will let the user select a timezone they are in, once a user has a timezone set it will put this on every page load.
//set the user's time zone for this page
date_default_timezone_set($_SESSION['time_zone']);
then I can use this to show the correct date/time based on that users timezone on that page with this code below, this example below has a timestamp I have set but it should come from the DB when done:
$time_stamp='1262291376'; //timestamp from Dec 31st 2009 3:35 EST and US navy site said it is Dec. 31, 20:35:56 UTC Universal Time
$date_format=date("m:d:y h:i:s A",$time_stamp); // formats timestamp in mm:dd:yy
echo $date_format;
The above code works perfect for taking the servers mysql time returned and converting it to my time based on my timezone but the problem I am having in building this is I cannot figure the best way to save a record to mysql as UTC time and also is a timestamp field better for this then a DATETIME in mysql? Please help with an example of how to save it to my mysql the correct way instead of using now() for the new setup
Thanks for any help
