0

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);
AJ.
  • 27,586
  • 18
  • 84
  • 94
DonWrk
  • 29
  • 6

3 Answers3

0

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]));
virtualeyes
  • 11,147
  • 6
  • 56
  • 91
  • Ok, here is the thing. I need to have a set time synced for everything on my site. I have a timed countdown so I will need a base time that is exactly the same that the countdown is counting down from and for the time that a purchase is made. Will it all be synced correctly down to the second? – DonWrk May 22 '11 at 17:51
  • When I'm displaying the purchase time to the user I do not want to display the date, only the time of day. Should I still store them single or separate? – DonWrk May 22 '11 at 17:58
  • well, if your server time is correct and it is that that you are using to compare the orderDateTime against, yes. Not sure what the "timed countdown" is about, but with server time and orderDateTime in MySQL you should be good to the second – virtualeyes May 22 '11 at 17:59
  • store in single column, does not matter, just omit "Y-m-d" from php date snippet I posted in my response – virtualeyes May 22 '11 at 17:59
  • To explain the timed countdown.... It counts down to when a deal expires. For examples it will show 3 hours 25 minutes and 45 seconds until the deal expires and counts down from the exact time I set. – DonWrk May 22 '11 at 18:02
  • that's fine, set your countdown relative to server time; that has nothing to do with timezone of user; i.e. "deal X expires in 45 minutes, buy now!" is the same for everyone in the world regardless of timezone. – virtualeyes May 22 '11 at 18:09
  • for displaying orderDateTime, however, you need to account for the user's timezone, sufficient info for how to achieve that in this page – virtualeyes May 22 '11 at 18:11
  • btw, as a new stackoverflow user, don't forget to flag an answer as answered and/or vote depending on value (to you) of response – virtualeyes May 22 '11 at 18:13
  • Don't worry I won't. I'm just trying to take in all the answers and see what's going to work for me. Thanks for your help to, I'm implementing this right now. – DonWrk May 22 '11 at 18:15
0

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

Community
  • 1
  • 1
Jodes
  • 14,118
  • 26
  • 97
  • 156
0

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
onteria_
  • 68,181
  • 7
  • 71
  • 64