Answers.Body: <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-20T19:33:30.183Z
Answers.LastActivityDate: 2016-09-20T19:33:30.183Z
Answers.UserDisplayName: Asia Seltzer
Body: <p>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.</p>
  
  <p>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 <code>2016-08-31 18:00:00</code> in Los Angeles, the <code>dateCreated</code> value in the database will be 7 hours later in the UTC/GMT timezone: <code>2016-09-01 01:00:00</code>.</p>
  
  <p>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:</p>
  
  <pre><code>$startDate = DateTime::createFromString('2016-08-01', craft()-&gt;timezone);
  $endDate   = DateTime::createFromString('2016-08-31', craft()-&gt;timezone);
  $endDate-&gt;modify('+1 day');
  
  $criteria         = $this-&gt;getElementCriteria();
  $criteria-&gt;limit  = null;
  $criteria-&gt;search = null;
  
  $query = craft()-&gt;elements-&gt;buildElementsQuery($criteria)
    -&gt;select('COUNT(elements.id) as value');
  </code></pre>
  
  <p>The above code gets handed off to the <code>ChartHelper::getRunChartDataFromQuery()</code> method which performs the query with this code:</p>
  
  <pre><code>$results = $query
    -&gt;addSelect("DATE_FORMAT({$dateColumn}, '{$sqlDateFormat}') as date")
    -&gt;andWhere(
      array('and', $dateColumn.' &gt;= :startDate', $dateColumn.' &lt; :endDate'),
      array(':startDate' =&gt; $startDate-&gt;mySqlDateTime(), ':endDate' =&gt; $endDate-&gt;mySqlDateTime()))
    -&gt;group($sqlGroup)
    -&gt;order($dateColumn.' asc')
    -&gt;queryAll();
  </code></pre>
  
  <p>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 <code>dateCreated</code> value in the database is actually on the following day, September 1st.</p>
  
  <p>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.</p>
  
  <p>Where do I need to adjust how the date values are being set in order to get back the right data?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-08-06T07:41:29.430Z
Comments.Text: I have the same issue, did you find a solution?
Comments.UserDisplayName: Bram Janssen
Tags: <datetime>
Title: How to query entries and take into account the timezone offset? - ChartHelper
ThreadText: # How to query entries and take into account the timezone offset? - ChartHelper
  
  (Asked by: username_0 on 2016-09-01)
  
  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?
  
  **Tags:** datetime
  
  > **Comments:**
  > **username_1**: I have the same issue, did you find a solution?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2016-09-20. Score: 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.
  
  
  ---
  
  