Mysql PHP Store and display time of purchase for the correct users timezone

I have a setup where I want to store the time of开发者_开发问答 a purchase down to the exact second. As of right now I use CURTIME() for this. Right now it returns the time in this format 16:03:59 what should I do if I want to store and then display the time in the correct time zone like 2:03 p.m.? Thanks.

$qry = "INSERT INTO purchases
   (id, qty, Date, Time, product_id, totalprice)
   VALUES('$id', '$qty', CURDATE(), CURTIME(),'$pid', '$price')";
$result = @mysql_query($qry);

---

Couple things:
1) would make sense to consolidate Date & Time columns into a single, say, orderDate column as MySQL date-time (then you can use simply, NOW() for the insert value)

2) PHP has great docs: http://php.net/manual/en/function.date.php

To set the timezone for UTC timezone site visitor:

date_default_timezone_set('UTC'); // replace with variable, probably stored in user's session

btw, not sure if MySQL provides setting timezone on-the-fly (probably set to server default), so you'll likely have to format the orderDate using php's date function above.

So, to sum up, in your query result loop using $q, your display could be:

echo date("Y-m-d h:i:s", strtotime($q[''orderDate]));

---

Firstly, I would store the value of time(), then do conversions to a string in the appropriate timezone as necessary. This makes it a little bit more elegant if the user changes his timezone while travelling for example.

To format for the users' timezone, you could get the timezone using Javascript, then pass it to PHP and format accordingly. This has been asked/answered before:

Javascript/PHP and timezones

---

I would store your time values in UTC using UTC_TIMESTAMP, then grab them using an ISO date format:

SELECT DATE_FORMAT(UTC_TIMESTAMP, GET_FORMAT(DATETIME, 'ISO'));

Which run a bit ago gives:

2011-05-22 17:53:23

So let's say the user is in the west coast, we can do something like this to so their local time:

$timeZone = 'America/Los_Angeles';
$dateSrc = '2011-05-22 17:53:23';

$dateTime = new DateTime($dateSrc, new DateTimeZone('GMT'));
$dateTime->setTimeZone(new DateTimeZone($timeZone));
echo "Result is: " . $dateTime->format('Y-m-d H:i:s');

Sample run:

$ php test.php 
Result is: 2011-05-22 10:53:23