How to query entries and take into account the timezone offset? - ChartHelper

Question

I'm trying to query a range of entries and my results are not returning all the entries in the range I am trying to target. My situation relates to displaying data on a chart using the Craft ChartHelper class, but I'm not sure if this issue is specific to that situation or more general.

I believe Craft saves records to the database based on the UTC timezone. So if I'm in Los Angeles (UTC-7) and save an Entry on 2016-08-31 18:00:00 in Los Angeles, the dateCreated value in the database will be 7 hours later in the UTC/GMT timezone: 2016-09-01 01:00:00.

Now, I want to query all entries that happened in the month of August. The following, largely taken from the Craft Commerce chart codebase, gets me roughly what I need – entries that range over the month of August:

$startDate = DateTime::createFromString('2016-08-01', craft()->timezone);
$endDate   = DateTime::createFromString('2016-08-31', craft()->timezone);
$endDate->modify('+1 day');

$criteria         = $this->getElementCriteria();
$criteria->limit  = null;
$criteria->search = null;

$query = craft()->elements->buildElementsQuery($criteria)
  ->select('COUNT(elements.id) as value');

The above code gets handed off to the ChartHelper::getRunChartDataFromQuery() method which performs the query with this code:

$results = $query
  ->addSelect("DATE_FORMAT({$dateColumn}, '{$sqlDateFormat}') as date")
  ->andWhere(
    array('and', $dateColumn.' >= :startDate', $dateColumn.' < :endDate'),
    array(':startDate' => $startDate->mySqlDateTime(), ':endDate' => $endDate->mySqlDateTime()))
  ->group($sqlGroup)
  ->order($dateColumn.' asc')
  ->queryAll();

While I get back a month's worth of entries, the query does not return entries that were saved after 5pm on August 31st in Los Angeles. I believe this is because the dateCreated value in the database is actually on the following day, September 1st.

I would like to adjust this query to return entries from within the month of the timezone of the website – in this case Los Angeles.

Where do I need to adjust how the date values are being set in order to get back the right data?

Answer 1

I'm not sure what kind of server you are running, but you should be able to set the server timezone in your control panel. You may need to then restart your database in order for you database's settings to be updated.