Refs #56

 - Added timezone setting per website. Added API to fetch the list of supported timezones. UTC Manual offsets are supported even if the PHP doesn't have timezone support built in. 
 - Added default timezone setting that the Super user can set. It will be used to pre-select timezones when creating new websites, or setting the default timezone when adding websites via the API without specifying the timezone.
 - Default timezone set to UTC to all existing websites on update. PHP Default timezone set to UTC in index.php and piwik.php.
 - Removed all usage of mysql date/time functions (which are internally doing timezone conversions based on mysql timezone), now using php generated timestamps (in UTC), or dates manually converted to the website timezone.
 - Altered the few Mysql fields that were defaulting to "Current timestamp", they now default to NULL.
 - Deprecated the field log_visit.server_date and log_conversion.server_date as they are now not used. Must use DATE(visit_last_action_time) instead. Note that the new INDEX on (idsite, visit_last_action_time, config_md5config) will greatly benefit the Live! plugin.
 - Deprecated Piwik_Date->get, must now use Piwik_Date->toString
 - Deprecated the DB adapters getCurrentTimezone() feature, as we now don't rely on the DB timezone. Also removed the warning from the install screen.

git-svn-id: http://dev.piwik.org/svn/trunk@2006 59fd770c-687e-43c8-a1e3-f5a4ff64c105

14 files changed, 335 insertions, 56 deletions

diff --git a/plugins/Actions/Actions.php b/plugins/Actions/Actions.php
index 371c0ce215..930e869b55 100644
--- a/plugins/Actions/Actions.php
+++ b/plugins/Actions/Actions.php
@@ -125,6 +125,7 @@ class Piwik_Actions extends Piwik_Plugin
 	public function archiveDay( $notification )
 	{
 		//TODO Actions should use integer based keys like other archive in piwik
+		/* @var $archiveProcessing Piwik_ArchiveProcessing */
 		$archiveProcessing = $notification->getNotificationObject();
 		
 		$this->actionsTablesByType = array(
@@ -155,11 +156,12 @@ class Piwik_Actions extends Piwik_Plugin
 					FROM (".$archiveProcessing->logTable." as t1
 						LEFT JOIN ".$archiveProcessing->logVisitActionTable." as t2 USING (idvisit))
 							LEFT JOIN ".$archiveProcessing->logActionTable." as t3 ON (t2.idaction_url = t3.idaction)
-					WHERE visit_server_date = ?
+					WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 						AND idsite = ?
 					GROUP BY t3.idaction
 					ORDER BY nb_hits DESC";
-		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ));
+		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ));
 		$modified = $this->updateActionsTableWithRowQuery($query);
 
 		/*
@@ -173,11 +175,12 @@ class Piwik_Actions extends Piwik_Plugin
 					FROM (".$archiveProcessing->logTable." as t1
 						LEFT JOIN ".$archiveProcessing->logVisitActionTable." as t2 USING (idvisit))
 							LEFT JOIN ".$archiveProcessing->logActionTable." as t3 ON (t2.idaction_name = t3.idaction)
-					WHERE visit_server_date = ?
+					WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 						AND idsite = ?
 					GROUP BY t3.idaction
 					ORDER BY nb_hits DESC";
-		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ));
+		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ));
 		$modified = $this->updateActionsTableWithRowQuery($query);
 
 		/*
@@ -192,11 +195,12 @@ class Piwik_Actions extends Piwik_Plugin
 							sum(case visit_total_actions when 1 then 1 else 0 end) as entry_bounce_count
 					FROM ".$archiveProcessing->logTable." 
 						JOIN ".$archiveProcessing->logActionTable." ON (visit_entry_idaction_url = idaction)
-					WHERE visit_server_date = ?
+					WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 						AND idsite = ?
 					GROUP BY visit_entry_idaction_url
 					";
-		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ));
+		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ));
 		$modified = $this->updateActionsTableWithRowQuery($query);
 		
 
@@ -210,11 +214,12 @@ class Piwik_Actions extends Piwik_Plugin
 							sum(case visit_total_actions when 1 then 1 else 0 end) as exit_bounce_count
 				 	FROM ".$archiveProcessing->logTable." 
 						JOIN ".$archiveProcessing->logActionTable." ON (visit_exit_idaction_url = idaction)
-				 	WHERE visit_server_date = ?
+				 	WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 				 		AND idsite = ?
 				 	GROUP BY visit_exit_idaction_url
 					";
-		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ));
+		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ));
 		$modified = $this->updateActionsTableWithRowQuery($query);
 		
 		/*
@@ -226,11 +231,12 @@ class Piwik_Actions extends Piwik_Plugin
 					FROM (".$archiveProcessing->logTable." log_visit 
 						JOIN ".$archiveProcessing->logVisitActionTable." log_link_visit_action USING (idvisit))
 							JOIN ".$archiveProcessing->logActionTable."  log_action ON (log_action.idaction = log_link_visit_action.idaction_url_ref)
-					WHERE visit_server_date = ?
+					WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 				 		AND idsite = ?
 				 	GROUP BY idaction_url_ref
 				";
-		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ));
+		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ));
 		$modified = $this->updateActionsTableWithRowQuery($query);
 		$this->archiveDayRecordInDatabase($archiveProcessing);
 	}
diff --git a/plugins/Installation/Controller.php b/plugins/Installation/Controller.php
index 9799931849..e7c948a04b 100644
--- a/plugins/Installation/Controller.php
+++ b/plugins/Installation/Controller.php
@@ -257,13 +257,6 @@ class Piwik_Installation_Controller extends Piwik_Controller
 			$this->session->charsetCorrection = true;
 		}
 
-		$dbTimezone = $db->getCurrentTimezone();
-		$phpTimezone = date('Z');
-		if($dbTimezone !== '' && ($dbTimezone != $phpTimezone))
-		{
-			$view->timezoneWarning = true;
-		}
-
 		$view->showNextStep = true;
 		$this->session->currentStepDone = __FUNCTION__;
 
@@ -801,6 +794,7 @@ class Piwik_Installation_Controller extends Piwik_Controller
 			$infos['integrityErrorMessages'] += array_slice($integrityInfo, 1);
 		}
 
+		$infos['timezone'] = Piwik::isTimezoneSupportEnabled();
 		return $infos;
 	}
 
diff --git a/plugins/Installation/templates/databaseCheck.tpl b/plugins/Installation/templates/databaseCheck.tpl
index c9eec3f3d7..3374582f74 100644
--- a/plugins/Installation/templates/databaseCheck.tpl
+++ b/plugins/Installation/templates/databaseCheck.tpl
@@ -27,19 +27,6 @@
 		<td class="label">{'Installation_DatabaseCreation'|translate}</td>
 		<td>{if isset($databaseCreated)}{$ok}{else}{$error}{/if}</td>
 	</tr>
-	<tr>
-		<td class="label">{'Installation_DatabaseTimezone'|translate}</td>
-		<td>{if isset($timezoneWarning)}{$warning}{else}{$ok}{/if}</td>
-	</tr>
-{if isset($timezoneWarning)}
-	<tr>
-		<td colspan="2">
-			<small>
-				<span style="color:#FF7F00">{'Installation_TimezoneMismatch'|translate:"<a href='misc/redirectToUrl.php?url=http://piwik.org/FAQ/troubleshooting/#faq_58' target='_blank'>FAQ</a>"}.</span>
-			</small>
-		</td>
-	</tr>
-{/if}
 </table>
 
 <p>
diff --git a/plugins/Installation/templates/systemCheck.tpl b/plugins/Installation/templates/systemCheck.tpl
index 6e9e1dfe42..11b0ed78dc 100644
--- a/plugins/Installation/templates/systemCheck.tpl
+++ b/plugins/Installation/templates/systemCheck.tpl
@@ -146,7 +146,7 @@
 			{else}
 				{$error} <i>{$infos.integrityErrorMessages[0]}</i>
 			{/if}
-			{if count($infos.integrityErrorMessages) > 1}
+			{if count($infos.integrityErrorMessages) >= 1}
 				<button id="more-results" class="ui-button ui-state-default ui-corner-all">{'General_Details'|translate}</button>
 			{/if}
 		{/if}
@@ -161,6 +161,13 @@
 		</td>
 	</tr>
 	<tr>
+		<td class="label">{'SitesManager_Timezone'|translate}</td>
+		<td>
+			{if $infos.timezone}{$ok}{else}{$warning} 
+				<br /><i>{'SitesManager_AdvancedTimezoneSupportNotFound'|translate}</i>{/if}	
+		</td>
+	</tr>
+	<tr>
 		<td class="label">{'Installation_SystemCheckOpenURL'|translate}</td>
 		<td>
 			{if $infos.openurl}{$infos.openurl} {$ok}{else}{$warning} <br /><i>{'Installation_SystemCheckOpenURLHelp'|translate}</i>{/if}
diff --git a/plugins/Live/Visitor.php b/plugins/Live/Visitor.php
index 388913e420..a78f9ca9c4 100644
--- a/plugins/Live/Visitor.php
+++ b/plugins/Live/Visitor.php
@@ -83,7 +83,7 @@ class Piwik_Live_Visitor
 
 	function getServerDate()
 	{
-		return $this->details['visit_server_date'];
+		return date('Y-m-d', strtotime($this->details['visit_last_action_time']));
 	}
 
 	function getIp()
diff --git a/plugins/Referers/Referers.php b/plugins/Referers/Referers.php
index 390c0b21eb..bddff604e8 100644
--- a/plugins/Referers/Referers.php
+++ b/plugins/Referers/Referers.php
@@ -150,7 +150,7 @@ class Piwik_Referers extends Piwik_Plugin
 		destroy($this->distinctUrls);
 	}
 	
-	protected function archiveDayAggregateVisits($archiveProcessing)
+	protected function archiveDayAggregateVisits(Piwik_ArchiveProcessing $archiveProcessing)
 	{
 		$query = "SELECT 	referer_type, 
 							referer_name, 
@@ -164,11 +164,12 @@ class Piwik_Referers extends Piwik_Plugin
 							sum(case visit_total_actions when 1 then 1 else 0 end) as bounce_count,
 							sum(case visit_goal_converted when 1 then 1 else 0 end) as nb_visits_converted
 				 	FROM ".$archiveProcessing->logTable."
-				 	WHERE visit_server_date = ?
+				 	WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 				 		AND idsite = ?
 				 	GROUP BY referer_type, referer_name, referer_url, referer_keyword
 				 	ORDER BY nb_visits DESC";
-		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ));
+		$query = $archiveProcessing->db->query($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ));
 
 		$this->interestBySearchEngine =
 			$this->interestByKeyword =
diff --git a/plugins/SitesManager/API.php b/plugins/SitesManager/API.php
index 3274f5673d..5ffe6a7ae4 100644
--- a/plugins/SitesManager/API.php
+++ b/plugins/SitesManager/API.php
@@ -32,6 +32,7 @@ class Piwik_SitesManager_API
 	}
 	
 	const OPTION_EXCLUDED_IPS_GLOBAL = 'SitesManager_ExcludedIpsGlobal';
+	const OPTION_DEFAULT_TIMEZONE = 'SitesManager_DefaultTimezone';
 	
 	/**
 	 * Returns the javascript tag for the given idSite.
@@ -221,10 +222,11 @@ class Piwik_SitesManager_API
 	 * 					    if several URLs are provided in the array, they will be recorded 
 	 * 						as Alias URLs for this website.
 	 * @param string Comma separated list of IPs to exclude from the reports (allows wildcards)
+	 * @param string Timezone string, eg. 'Europe/London'
 	 * 
 	 * @return int the website ID created
 	 */
-	public function addSite( $siteName, $urls, $excludedIps = null )
+	public function addSite( $siteName, $urls, $excludedIps = null, $timezone = null )
 	{
 		Piwik::checkUserIsSuperUser();
 		
@@ -232,6 +234,13 @@ class Piwik_SitesManager_API
 		$urls = $this->cleanParameterUrls($urls);
 		$this->checkUrls($urls);
 		$this->checkAtLeastOneUrl($urls);
+		$timezone = trim($timezone);
+		
+		if(empty($timezone))
+		{
+			$timezone = $this->getDefaultTimezone();
+		}
+		$this->checkValidTimezone($timezone);
 		
 		$db = Zend_Registry::get('db');
 		
@@ -240,9 +249,11 @@ class Piwik_SitesManager_API
 		
 		$bind = array(	'name' => $siteName,
 						'main_url' => $url,
+						'ts_created' => Piwik_Date::now()->getDatetime()
 		);
 	
 		$bind['excluded_ips'] = $this->checkAndReturnExcludedIps($excludedIps);
+		$bind['timezone'] = $timezone;
 		$db->insert(Piwik::prefixTable("site"), $bind);
 									
 		$idSite = $db->lastInsertId();
@@ -312,6 +323,22 @@ class Piwik_SitesManager_API
 		}
 	}
 
+	private function checkValidTimezone($timezone)
+	{
+		$timezones = $this->getTimezonesList();
+		foreach($timezones as $continent => $cities)
+		{
+			foreach($cities as $timezoneId => $city)
+			{
+				if($timezoneId == $timezone)
+				{
+					return true;
+				}
+			}
+		}
+		throw new Exception('The timezone "'.$timezone.'" is not valid. Please enter a valid timezone.');
+	}
+	
 	/**
 	 * Checks that the submitted IPs (comma separated list) are valid
 	 * Returns the cleaned up IPs
@@ -384,6 +411,37 @@ class Piwik_SitesManager_API
 	}
 	
 	/**
+	 * Returns the default timezone that will be set when creating a website through the API.
+	 * Via the UI, if the default timezone is not UTC, it will be pre-selected in the drop down
+	 * 
+	 * @return string Timezone eg. UTC+7 or Europe/Paris
+	 */
+	public function getDefaultTimezone()
+	{
+		Piwik::checkUserIsSuperUser();
+		$defaultTimezone = Piwik_GetOption(self::OPTION_DEFAULT_TIMEZONE);
+		if($defaultTimezone)
+		{
+			return $defaultTimezone;
+		}
+		return 'UTC';
+	}
+	
+	/**
+	 * Sets the default timezone that will be used when creating websites
+	 * 
+	 * @param $timezone string eg. Europe/Paris or UTC+8
+	 * @return bool
+	 */
+	public function setDefaultTimezone($defaultTimezone)
+	{
+		Piwik::checkUserIsSuperUser();
+		$this->checkValidTimezone($defaultTimezone);
+		Piwik_SetOption(self::OPTION_DEFAULT_TIMEZONE, $defaultTimezone);
+		return true;
+	}
+	
+	/**
 	 * Update an existing website.
 	 * If only one URL is specified then only the main url will be updated.
 	 * If several URLs are specified, both the main URL and the alias URLs will be updated.
@@ -392,12 +450,13 @@ class Piwik_SitesManager_API
 	 * @param string website name
 	 * @param string|array the website URLs
 	 * @param string Comma separated list of IPs to exclude from being tracked (allows wildcards)
+	 * @param string Timezone
 	 * 
 	 * @exception if any of the parameter is not correct
 	 * 
 	 * @return bool true on success
 	 */
-	public function updateSite( $idSite, $siteName, $urls = null, $excludedIps = null)
+	public function updateSite( $idSite, $siteName, $urls = null, $excludedIps = null, $timezone = null)
 	{
 		Piwik::checkUserHasAdminAccess($idSite);
 
@@ -415,6 +474,14 @@ class Piwik_SitesManager_API
 			
 			$bind['main_url'] = $url;
 		}
+		
+		if(!is_null($timezone))
+		{
+			$timezone = trim($timezone);
+			$this->checkValidTimezone($timezone);
+			$bind['timezone'] = $timezone;
+		}
+		
 		$bind['excluded_ips'] = $this->checkAndReturnExcludedIps($excludedIps);
 		$bind['name'] = $siteName;
 		$db = Zend_Registry::get('db');
@@ -433,6 +500,72 @@ class Piwik_SitesManager_API
 	}
 	
 	/**
+	 * Returns the list of timezones supported. 
+	 * Used for addSite and updateSite
+	 * 
+	 * @TODO NOT COMPATIBLE WITH API RESPONSE AUTO BUILDER
+	 * 
+	 * @return array of timezone strings
+	 */
+	public function getTimezonesList()
+	{
+		if(!Piwik::isTimezoneSupportEnabled())
+		{
+			return array('UTC' => $this->getTimezonesListUTCOffsets());
+		}
+		
+		$continents = array( 'Africa', 'America', 'Antarctica', 'Arctic', 'Asia', 'Atlantic', 'Australia', 'Europe', 'Indian', 'Pacific');
+		$timezones = timezone_identifiers_list();
+		
+		$return = array();
+		foreach($timezones as $timezone)
+		{
+			$timezoneExploded = explode('/', $timezone);
+			$continent = $timezoneExploded[0];
+			
+			// only display timezones that are grouped by continent
+			if(!in_array($continent, $continents))
+			{
+				continue;
+			}
+			$city = $timezoneExploded[1];
+			if(!empty($timezoneExploded[2]))
+			{
+				$city .= ' - '.$timezoneExploded[2];
+			}
+			$city = str_replace('_', ' ', $city);
+			$return[$continent][$timezone] = $city;
+		}
+		
+		$return['UTC'] = $this->getTimezonesListUTCOffsets();
+		return $return;
+	}
+	
+	private function getTimezonesListUTCOffsets()
+	{
+		// manually add the UTC offsets
+		$GmtOffsets = array (-12, -11.5, -11, -10.5, -10, -9.5, -9, -8.5, -8, -7.5, -7, -6.5, -6, -5.5, -5, -4.5, -4, -3.5, -3, -2.5, -2, -1.5, -1, -0.5,
+			0, 0.5, 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5, 5.5, 5.75, 6, 6.5, 7, 7.5, 8, 8.5, 8.75, 9, 9.5, 10, 10.5, 11, 11.5, 12, 12.75, 13, 13.75, 14);
+			
+		$return = array();
+		foreach($GmtOffsets as $offset)
+		{
+			if($offset > 0)
+			{
+				$offset = '+'.$offset;
+			}
+			elseif($offset == 0)
+			{
+				$offset = '';
+			}
+			$offset = 'UTC' . $offset;
+			$offsetName = str_replace(array('.25','.5','.75'), array(':15',':30',':45'), $offset);
+			$return[$offset] = $offsetName;
+		}
+		return $return;
+	}
+	
+	/**
 	 * Insert the list of alias URLs for the website.
 	 * The URLs must not exist already for this website!
 	 */
diff --git a/plugins/SitesManager/Controller.php b/plugins/SitesManager/Controller.php
index 2647008093..140de42455 100644
--- a/plugins/SitesManager/Controller.php
+++ b/plugins/SitesManager/Controller.php
@@ -26,10 +26,21 @@ class Piwik_SitesManager_Controller extends Piwik_Controller
 			$site['excluded_ips'] = str_replace(',','<br/>', $site['excluded_ips']);
 		}
 		$view->adminSites = $sites;
-		$view->currentIpAddress = Piwik_Common::getIpString();
-		$this->setGeneralVariablesView($view);
+		
+		try {
+			$timezones = Piwik_SitesManager_API::getInstance()->getTimezonesList();
+		} catch(Exception $e) {
+			$timezones = array();
+		}
+		$view->timezoneSupported = Piwik::isTimezoneSupportEnabled();
+		$view->timezones = json_encode($timezones);
+		$view->utcTime = Piwik_Date::now()->getDatetime();
 		$excludedIpsGlobal = Piwik_SitesManager_API::getInstance()->getExcludedIpsGlobal();
 		$view->globalExcludedIps = str_replace(',',"\n", $excludedIpsGlobal);
+		$view->defaultTimezone = Piwik_SitesManager_API::getInstance()->getDefaultTimezone();
+		$view->currentIpAddress = Piwik_Common::getIpString();
+
+		$this->setGeneralVariablesView($view);
 		$view->menu = Piwik_GetAdminMenu();
 		echo $view->render();
 	}
diff --git a/plugins/SitesManager/templates/SitesManager.js b/plugins/SitesManager/templates/SitesManager.js
index 1c06c8c4ac..f60b0b615a 100644
--- a/plugins/SitesManager/templates/SitesManager.js
+++ b/plugins/SitesManager/templates/SitesManager.js
@@ -24,13 +24,15 @@ function getAddSiteAJAX( row )
 	var urls = getApiFormatUrls(urls);
 	var excludedIps = $(row).find('textarea#siteadd_excludedIps').val();
 	excludedIps = getApiFormatExcludedIps(excludedIps);
- 	
+	var timezone = encodeURIComponent($(row).find('option:selected').val());
+	
 	var request = '';
 	request += '&module=API';
 	request += '&format=json';
 	request += '&method=SitesManager.addSite';
 	siteName = encodeURIComponent(siteName);
 	request += '&siteName='+siteName;
+	request += '&timezone='+timezone;
 	request += '&excludedIps='+excludedIps;
 	$.each(urls, function (key,value){ request+= '&urls[]='+escape(value);} );
  	request += '&token_auth=' + piwik.token_auth;
@@ -59,7 +61,7 @@ function getUpdateSiteAJAX( row )
 	urls = getApiFormatUrls(urls);
 	var excludedIps = $(row).find('textarea#excludedIps').val();
 	excludedIps = getApiFormatExcludedIps(excludedIps);
-	
+	var timezone = encodeURIComponent($(row).find('option:selected').val());
 	var request = '';
 	request += '&module=API';
 	request += '&format=json';
@@ -67,6 +69,7 @@ function getUpdateSiteAJAX( row )
 	siteName = encodeURIComponent(siteName);
 	request += '&siteName='+siteName;
 	request += '&idSite='+idSite;
+	request += '&timezone='+timezone;
 	request += '&excludedIps='+excludedIps;
 	$.each(urls, function (key,value){ if(value.length>1) request+= '&urls[]='+value;} );
  	request += '&token_auth=' + piwik.token_auth;
@@ -76,6 +79,20 @@ function getUpdateSiteAJAX( row )
 	return ajaxRequest;
 }
 
+function getDefaultTimezoneAJAX()
+{
+	var ajaxRequest = piwikHelper.getStandardAjaxConf('ajaxLoadingDefaultTimezone', 'ajaxErrorDefaultTimezone');
+	var timezone = encodeURIComponent($('#defaultTimezone option:selected').val());
+	var request = '';
+	request += '&module=API';
+	request += '&format=json';
+	request += '&method=SitesManager.setDefaultTimezone';
+	request += '&defaultTimezone='+timezone;
+ 	request += '&token_auth=' + piwik.token_auth;
+	ajaxRequest.data = request;
+	return ajaxRequest;
+}
+
 function getSetGlobalExcludedIpsAJAX()
 {
 	var ajaxRequest = piwikHelper.getStandardAjaxConf('ajaxLoadingExcludedIps', 'ajaxErrorExcludedIps');
@@ -104,6 +121,7 @@ $(document).ready( function() {
 				<td><input id="siteadd_name" value="Name" size="25" /></td>\
 				<td><textarea cols="30" rows="3" id="siteadd_urls">http://siteUrl.com/\nhttp://siteUrl2.com/</textarea><br />'+aliasUrlsHelp+'</td>\
 				<td><textarea cols="30" rows="3" id="siteadd_excludedIps"></textarea><br />'+excludedIpHelp+'</td>\
+				<td>'+getTimezoneSelector(defaultTimezone)+'<br />' + timezoneHelp + '</td>\
 				<td><img src="plugins/UsersManager/images/ok.png" class="addsite" href="#" /></td>\
 	  			<td><img src="plugins/UsersManager/images/remove.png" class="cancel" /></td>\
 	 		</tr>')
@@ -159,6 +177,12 @@ $(document).ready( function() {
 						contentAfter += '<br />'+excludedIpHelp;
 						$(n).html(contentAfter);
 					}
+					if(idName == 'timezone')
+					{
+						var contentAfter = getTimezoneSelector(contentBefore);
+						contentAfter += '<br />' + timezoneHelp;
+						$(n).html(contentAfter);
+					}
 				}
 			);
 			$(this)
@@ -173,10 +197,39 @@ $(document).ready( function() {
 	$('#globalExcludedIpsSubmit').click( function() {
 		$.ajax( getSetGlobalExcludedIpsAJAX() );
 	});
+
+	$('#defaultTimezone').html( getTimezoneSelector(defaultTimezone));
+	
+	$('#defaultTimezoneSubmit').click( function() {
+		$.ajax( getDefaultTimezoneAJAX() );
+	});
 	
 	$('td.editableSite').click( function(){ $(this).parent().find('.editSite').click(); } );
 });
- 
+
+function getTimezoneSelector(selectedTimezone)
+{
+	var html = '<select id="timezones">';
+	if(!selectedTimezone)
+	{
+		html += '<option selected="selected" value="'+selectACity+'">'+selectACity+'</option>';
+	}
+	for(var continent in timezones) {
+		html += '<optgroup label="' + continent + '">';
+		for(var timezoneId in timezones[continent]) {
+			var selected = '';
+			if(timezoneId == selectedTimezone) {
+				selected = ' selected="selected" ';
+			}
+			html += '<option ' + selected + ' value="'+ timezoneId + '">' + timezones[continent][timezoneId] + '</option>';
+		}
+		html += "</optgroup>\n";
+	}
+	html += '</select>';
+	return html;
+}
+
+
 function submitSiteOnEnter(e)
 {
 	var key=e.keyCode || e.which;
diff --git a/plugins/SitesManager/templates/SitesManager.tpl b/plugins/SitesManager/templates/SitesManager.tpl
index 3331aacd63..43607ab226 100644
--- a/plugins/SitesManager/templates/SitesManager.tpl
+++ b/plugins/SitesManager/templates/SitesManager.tpl
@@ -9,7 +9,26 @@
 {assign var=excludedIpHelp value=$excludedIpHelpPlain|inlineHelp}
 var excludedIpHelp = '{$excludedIpHelp|escape:javascript}';
 var aliasUrlsHelp = '{'SitesManager_AliasUrlHelp'|translate|inlineHelp|escape:javascript}';
+{capture assign=defaultTimezoneHelpPlain}
+	{if $timezoneSupported}
+		{'SitesManager_ChooseCityInSameTimezoneAsYou'|translate}
+	{else}
+		{'SitesManager_AdvancedTimezoneSupportNotFound'|translate}
+	{/if} <br /><br />{'SitesManager_UTCTimeIs'|translate:$utcTime}
+{/capture}
+
+{capture assign=timezoneHelpPlain}
+	{$defaultTimezoneHelpPlain}
+	<br /><br />{'SitesManager_ChangingYourTimezoneWillOnlyAffectDataForward'|translate}
+{/capture}
+
+var timezoneHelp = '{$timezoneHelpPlain|inlineHelp|escape:javascript}';
+{assign var=defaultTimezoneHelp value=$defaultTimezoneHelpPlain|inlineHelp};
+var timezones = {$timezones};
+var defaultTimezone = '{$defaultTimezone}';
+var selectACity = '{'SitesManager_SelectCity'|translate}';
 </script>
+
 <script type="text/javascript" src="plugins/SitesManager/templates/SitesManager.js"></script>
 
 {literal}
@@ -29,18 +48,22 @@ var aliasUrlsHelp = '{'SitesManager_AliasUrlHelp'|translate|inlineHelp|escape:ja
 #editSites {
 	valign: top;
 }
+option, select {
+	font-size:11px;
+}
 </style>
 {/literal}
 
 <h2>{'SitesManager_WebsitesManagement'|translate}</h2>
 <p>{'SitesManager_MainDescription'|translate}
 {if $isSuperUser}
-<br/>{'SitesManager_SuperUserCanExcludeIpsOnAllWebsites'|translate:"<a href='#globalIpExclusion'>":"</a>"}.
+<br/>{'SitesManager_SuperUserCan'|translate:"<a href='#globalIpExclusion'>":"</a>":"<a href='#defaultTimezone'>":"</a>"}
 {/if}
 </p>
 {ajaxErrorDiv}
 {ajaxLoadingDiv}
 
+
 {if $adminSites|@count == 0}
 	{'SitesManager_NoWebsites'|translate}
 {else}
@@ -51,6 +74,7 @@ var aliasUrlsHelp = '{'SitesManager_AliasUrlHelp'|translate|inlineHelp|escape:ja
 			<th>{'SitesManager_Name'|translate}</th>
 			<th>{'SitesManager_Urls'|translate}</th>
 			<th>{'SitesManager_ExcludedIps'|translate}</th>
+			<th>{'SitesManager_Timezone'|translate}</th>
 			<th> </th>
 			<th> </th>
 			<th> {'SitesManager_JsTrackingTag'|translate} </th>
@@ -63,6 +87,7 @@ var aliasUrlsHelp = '{'SitesManager_AliasUrlHelp'|translate|inlineHelp|escape:ja
 				<td id="siteName" class="editableSite">{$site.name}</td>
 				<td id="urls" class="editableSite">{foreach from=$site.alias_urls item=url}{$url}<br />{/foreach}</td>       
 				<td id="excludedIps" class="editableSite">{foreach from=$site.excluded_ips item=ip}{$ip}<br />{/foreach}</td>       
+				<td id="timezone" class="editableSite">{$site.timezone}</td>       
 				<td><img src='plugins/UsersManager/images/edit.png' class="editSite" id="row{$i}" href='#' title="{'General_Edit'|translate}" /></td>
 				<td><img src='plugins/UsersManager/images/remove.png' class="deleteSite" id="row{$i}" title="{'General_Delete'|translate}" value="{'General_Delete'|translate}" /></td>
 				<td><a href='{url action=displayJavascriptCode idsite=$site.idsite}'>{'SitesManager_ShowTrackingTag'|translate}</a></td>
@@ -85,6 +110,19 @@ var aliasUrlsHelp = '{'SitesManager_AliasUrlHelp'|translate|inlineHelp|escape:ja
 	{$excludedIpHelp}
 	<input type="hidden" name="token_auth" value="{$token_auth}" />
 	<p><input type="submit" class="submit" id='globalExcludedIpsSubmit' value="{'General_Save'|translate}" /></p>
+	
+	
+	<a name='defaultTimezone'></a><h2>{'SitesManager_DefaultTimezone'|translate}</h2>
+	<p>{'SitesManager_SelectDefaultTimezone'|translate} 
+	{ajaxErrorDiv id=ajaxErrorDefaultTimezone}
+	{ajaxLoadingDiv id=ajaxLoadingDefaultTimezone}
+	<div id='defaultTimezone'></div>
+	<br/>
+	{$defaultTimezoneHelp}
+	<input type="hidden" name="token_auth" value="{$token_auth}" />
+	<p><input type="submit" class="submit" id='defaultTimezoneSubmit' value="{'General_Save'|translate}" /></p>
+	
+	
 {/if}
 
 <br /><br /><br /><br />
diff --git a/plugins/SitesManager/tests/SitesManager.test.php b/plugins/SitesManager/tests/SitesManager.test.php
index eb090a5c0c..642a6cf970 100644
--- a/plugins/SitesManager/tests/SitesManager.test.php
+++ b/plugins/SitesManager/tests/SitesManager.test.php
@@ -19,6 +19,9 @@ class Test_Piwik_SitesManager extends Test_Database
     	$pseudoMockAccess = new FakeAccess;
 		FakeAccess::$superUser = true;
 		Zend_Registry::set('access', $pseudoMockAccess);
+		
+		// clear static Site cache
+		Piwik_Site::clearCache();
     }
     
     /**
@@ -107,12 +110,14 @@ class Test_Piwik_SitesManager extends Test_Database
     /**
      * Test with valid IPs
      */
-    public function test_addSite_excludedIps_valid()
+    public function test_addSite_excludedIpsAndtimezone_valid()
     {
-    	$ips = '1.2.3.4,1.1.1.*,1.2.*.*,1.*.*.*'; 
-		$idsite = Piwik_SitesManager_API::getInstance()->addSite("name","http://piwik.net/", $ips);
+    	$ips = '1.2.3.4,1.1.1.*,1.2.*.*,1.*.*.*';
+    	$timezone = 'Europe/Paris'; 
+		$idsite = Piwik_SitesManager_API::getInstance()->addSite("name","http://piwik.net/", $ips, $timezone);
     	$siteInfo = Piwik_SitesManager_API::getInstance()->getSiteFromId($idsite);
     	$this->assertEqual($siteInfo['excluded_ips'], $ips);
+    	$this->assertEqual($siteInfo['timezone'], $timezone);
     }
     
     /**
@@ -482,8 +487,8 @@ class Test_Piwik_SitesManager extends Test_Database
     	$idsite = Piwik_SitesManager_API::getInstance()->addSite("site3",array("http://piwik.org"));
     	
     	$resultWanted = array(
-    		0 => array("idsite" => 1, "name" => "site1", "main_url" =>"http://piwik.net", "excluded_ips" => ""),
-    		1 => array("idsite" => 3, "name" => "site3", "main_url" =>"http://piwik.org", "excluded_ips" => ""),
+    		0 => array("idsite" => 1, "name" => "site1", "main_url" =>"http://piwik.net", "excluded_ips" => "", 'timezone' => 'UTC'),
+    		1 => array("idsite" => 3, "name" => "site3", "main_url" =>"http://piwik.org", "excluded_ips" => "", 'timezone' => 'UTC'),
     	);
     		
 		FakeAccess::setIdSitesAdmin (array(1,3));
@@ -518,8 +523,8 @@ class Test_Piwik_SitesManager extends Test_Database
     	$idsite = Piwik_SitesManager_API::getInstance()->addSite("site3",array("http://piwik.org"));
     	
     	$resultWanted = array(
-    		0 => array("idsite" => 1, "name" => "site1", "main_url" =>"http://piwik.net", "excluded_ips" => ""),
-    		1 => array("idsite" => 3, "name" => "site3", "main_url" =>"http://piwik.org", "excluded_ips" => ""),
+    		0 => array("idsite" => 1, "name" => "site1", "main_url" =>"http://piwik.net", "excluded_ips" => "", 'timezone' => 'UTC'),
+    		1 => array("idsite" => 3, "name" => "site3", "main_url" =>"http://piwik.org", "excluded_ips" => "", 'timezone' => 'UTC'),
     	);
     		
 		FakeAccess::setIdSitesView (array(1,3));
@@ -554,8 +559,8 @@ class Test_Piwik_SitesManager extends Test_Database
     	$idsite = Piwik_SitesManager_API::getInstance()->addSite("site3",array("http://piwik.org"));
     	
     	$resultWanted = array(
-    		0 => array("idsite" => 1, "name" => "site1", "main_url" =>"http://piwik.net", "excluded_ips" => ""),
-    		1 => array("idsite" => 3, "name" => "site3", "main_url" =>"http://piwik.org", "excluded_ips" => ""),
+    		0 => array("idsite" => 1, "name" => "site1", "main_url" =>"http://piwik.net", "excluded_ips" => "", 'timezone' => 'UTC'),
+    		1 => array("idsite" => 3, "name" => "site3", "main_url" =>"http://piwik.org", "excluded_ips" => "", 'timezone' => 'UTC'),
     	);
     		
 		FakeAccess::setIdSitesView (array(1,3));
@@ -686,5 +691,41 @@ class Test_Piwik_SitesManager extends Test_Database
     	$this->assertEqual($allUrls,$newurls);
 
     }
+
+    function test_addSites_invalidTimezone()
+    {
+    	// trying invalid timezones
+    	try {
+    		$idsite = Piwik_SitesManager_API::getInstance()->addSite("site1",array('http://example.org'), '', 'UTC+15');
+    		$this->fail('invalid timezone should raise an exception');
+    	} catch(Exception $e) {
+    	}
+    	try {
+    		$idsite = Piwik_SitesManager_API::getInstance()->addSite("site1",array('http://example.org'), '', 'Paris');
+    		$this->fail('invalid timezone should raise an exception');
+    	} catch(Exception $e) {
+    	}
+    	$this->pass();
+    }
+    
+    function test_setDefaultTimezone()
+    {
+    	$defaultTimezone = Piwik_SitesManager_API::getInstance()->getDefaultTimezone();
+    	$this->assertEqual($defaultTimezone, 'UTC');
+    
+    	$idsite = Piwik_SitesManager_API::getInstance()->addSite("site1",array('http://example.org'), '');
+    	$site = new Piwik_Site($idsite);
+    	$this->assertEqual($site->getTimezone(), 'UTC');
+    	
+    	$newDefaultTimezone = 'UTC+5.5';
+    	Piwik_SitesManager_API::getInstance()->setDefaultTimezone($newDefaultTimezone);
+    	$defaultTimezone = Piwik_SitesManager_API::getInstance()->getDefaultTimezone();
+    	$this->assertEqual($defaultTimezone, $newDefaultTimezone);
+    	
+    	$idsite = Piwik_SitesManager_API::getInstance()->addSite("site1",array('http://example.org'), '', $newDefaultTimezone);
+    	$site = new Piwik_Site($idsite);
+    	$this->assertEqual($site->getTimezone(), $newDefaultTimezone);
+    	$this->assertEqual($site->getCreationDate(), date('Y-m-d'));
+    }
 }
 
diff --git a/plugins/UsersManager/API.php b/plugins/UsersManager/API.php
index a96806ae16..94ed2d98c4 100644
--- a/plugins/UsersManager/API.php
+++ b/plugins/UsersManager/API.php
@@ -17,6 +17,10 @@
 class Piwik_UsersManager_API 
 {
 	static private $instance = null;
+	
+	/**
+	 * @return Piwik_UsersManager_API
+	 */
 	static public function getInstance()
 	{
 		if (self::$instance == null)
@@ -295,7 +299,8 @@ class Piwik_UsersManager_API
 									'alias' => $alias,
 									'email' => $email,
 									'token_auth' => $token_auth,
-									)
+									'date_registered' => Piwik_Date::now()->getDatetime()
+							)
 		);
 		
 		// we reload the access list which doesn't yet take in consideration this new user
diff --git a/plugins/UsersManager/tests/UsersManager.test.php b/plugins/UsersManager/tests/UsersManager.test.php
index 81dc536511..d4b802199c 100644
--- a/plugins/UsersManager/tests/UsersManager.test.php
+++ b/plugins/UsersManager/tests/UsersManager.test.php
@@ -364,11 +364,12 @@ class Test_Piwik_UsersManager extends Test_Database
     	$email = "mgeag4544i@geq.com";
     	$alias = "her is my alias )(&|\" '£%*(&%+))";
 		
+    	$time = time();
 		Piwik_UsersManager_API::getInstance()->addUser($login, $password, $email, $alias);
     	$user = Piwik_UsersManager_API::getInstance()->getUser($login);
 		
 	    // check that the date registered is correct
-		$this->assertTrue( strtotime($user['date_registered']) >= time() - 1 , 
+		$this->assertTrue( strtotime($user['date_registered']) ==  $time , 
 				"the date_registered ".strtotime($user['date_registered'])." is different from the time() ". time());
 		$this->assertTrue($user['date_registered'] <= time() );
 		
diff --git a/plugins/VisitFrequency/VisitFrequency.php b/plugins/VisitFrequency/VisitFrequency.php
index 58d9e45c9f..16d3126cee 100644
--- a/plugins/VisitFrequency/VisitFrequency.php
+++ b/plugins/VisitFrequency/VisitFrequency.php
@@ -67,6 +67,7 @@ class Piwik_VisitFrequency extends Piwik_Plugin
 	
 	function archiveDay($notification)
 	{
+		/* @var $archiveProcessing Piwik_ArchiveProcessing */
 		$archiveProcessing = $notification->getNotificationObject();
 		
 		$query = "SELECT 	count(distinct visitor_idcookie) as nb_uniq_visitors_returning,
@@ -77,10 +78,11 @@ class Piwik_VisitFrequency extends Piwik_Plugin
 							sum(case visit_total_actions when 1 then 1 else 0 end) as bounce_count_returning,
 							sum(case visit_goal_converted when 1 then 1 else 0 end) as nb_visits_converted_returning
 				 	FROM ".$archiveProcessing->logTable."
-				 	WHERE visit_server_date = ?
+				 	WHERE visit_last_action_time >= ?
+						AND visit_last_action_time <= ?
 				 		AND idsite = ?
 				 		AND visitor_returning = 1";
-		$row = $archiveProcessing->db->fetchRow($query, array( $archiveProcessing->strDateStart, $archiveProcessing->idsite ) );
+		$row = $archiveProcessing->db->fetchRow($query, array( $archiveProcessing->getStartDatetimeUTC(), $archiveProcessing->getEndDatetimeUTC(), $archiveProcessing->idsite ) );
 		
 		if($row === false || $row === null)
 		{