title: Date insertion in Cassandra DB: non-trivial 1h shift issue
body: <p>I am a bit desperate about this problem... I have no idea how to face it.</p>
  
  <p>Here is a simpler way to look at this problem:</p>
  
  <p>If my insert cql query is:</p>
  
  <blockquote>
    <p>"BEGIN BATCH USING CONSISTENCY ONE insert into my_table(id,'2014-04-11 8:00:00',...,'2014-04-15 10:00:00') values ('2036548',3.15,...,4.11) APPLY BATCH"</p>
  </blockquote>
  
  <p>...and my data request cql query is:</p>
  
  <blockquote>
    <p>"Select FIRST 100000 '2014-04-01 0:00:00'..'2014-04-16 0:00:00' from my_table where id=2036548"</p>
  </blockquote>
  
  <p>...why does the inserted date <em>2014-04-15 <strong>10</strong>:00:00</em> changes to <em>2014-04-15 <strong>11</strong>:00:00</em> when pullling it from Cassandra?</p>
  
  <p>The date pulling code in vb.net is:</p>
  
  <pre><code>Public Shared Function getCassandraDate(ByVal value As Byte()) As Date
      Dim buffer As Byte() = New Byte(value.Length - 1) {}
      value.CopyTo(buffer, 0)
      Array.Reverse(buffer)
      Dim ticks As Long = BitConverter.ToInt64(buffer, 0)
      Dim dateTime As New System.DateTime(1970, 1, 1, 0, 0, 0, _
           0)
  
      dateTime = dateTime.AddMilliseconds(ticks)
      Return dateTime.ToLocalTime
  End Function
  </code></pre>
  
  <p>...same thing in PHP:</p>
  
  <pre><code>date_default_timezone_set("Europe/Paris");
  $time = $this-&gt;unpackDate($packed_time);
  $str_time = date('Y-m-d H:i:s',$time); //TODO : to local time
  
  private function unpackDate($data, $is_name=null)
  {
      $arr = unpack('N2', $data);
  
      // If we are on a 32bit architecture we have to explicitly deal with
      // 64-bit twos-complement arithmetic since PHP wants to treat all ints
      // as signed and any int over 2^31 - 1 as a float
      if (PHP_INT_SIZE == 4) {
  
          $hi = $arr[1];
          $lo = $arr[2];
          $isNeg = $hi  &lt; 0;
  
          // Check for a negative
          if ($isNeg) {
              $hi = ~$hi &amp; (int)0xffffffff;
              $lo = ~$lo &amp; (int)0xffffffff;
  
              if ($lo == (int)0xffffffff) {
                  $hi++;
                  $lo = 0;
              } else {
                  $lo++;
              }
          }
  
          // Force 32bit words in excess of 2G to pe positive - we deal wigh sign
          // explicitly below
  
          if ($hi &amp; (int)0x80000000) {
              $hi &amp;= (int)0x7fffffff;
              $hi += 0x80000000;
          }
  
          if ($lo &amp; (int)0x80000000) {
              $lo &amp;= (int)0x7fffffff;
              $lo += 0x80000000;
          }
  
          $value = $hi * 4294967296 + $lo;
  
          if ($isNeg)
              $value = 0 - $value;
  
      } else {
          // Upcast negatives in LSB bit
          if ($arr[2] &amp; 0x80000000)
              $arr[2] = $arr[2] &amp; 0xffffffff;
  
          // Check for a negative
          if ($arr[1] &amp; 0x80000000) {
              $arr[1] = $arr[1] &amp; 0xffffffff;
              $arr[1] = $arr[1] ^ 0xffffffff;
              $arr[2] = $arr[2] ^ 0xffffffff;
              $value = 0 - $arr[1]*4294967296 - $arr[2] - 1;
          } else {
              $value = $arr[1]*4294967296 + $arr[2];
          }
      }
      return $value / 1e3;
  }
  </code></pre>
  
  <hr>
  
  <p><strong>MORE DETAILS</strong></p>
  
  <p><strong>Processing chain:</strong></p>
  
  <p>(1). insertion to Cassandra through .NET</p>
  
  <p>(2). Cassandra data storage</p>
  
  <p>(3). Pulling the data from PHP or .NET</p>
  
  <p><strong>Problem:</strong></p>
  
  <p>As for today, a date being <em>2014-04-15 <strong>10</strong>:00:00</em> in step (1), will come out as <em>2014-04-15 <strong>11</strong>:00:00</em> in step (3).</p>
  
  <p><strong>Details:</strong></p>
  
  <p>(regarding the date format in this chain)</p>
  
  <p>(1). Local time in .NET (Timezone: "Europe/Paris"). Insertion cql that is being executed: <code>"BEGIN BATCH USING CONSISTENCY ONE insert into my_table(id,'2014-04-11 8:00:00',...,'2014-04-15 10:00:00') values ('2036548',3.15,...,4.11) APPLY BATCH"</code></p>
  
  <p>(2). <em>??? I don't know what Cassandra does here... ???</em></p>
  
  <p>(3). Example of cql query to pull the data: <code>"Select FIRST 100000 '2014-04-01 0:00:00'..'2014-04-16 0:00:00' from my_table where id=2036548"</code>. In php: <code>date_default_timezone_set("Europe/Paris"); $str_time = date('Y-m-d H:i:s',$time);</code>. In .NET: <code>dateTime.ToLocalTime</code>.</p>
  
  <p><strong>Extra info:</strong></p>
  
  <p>I think it worked well before the daylight saving time change some weeks ago. But I can not be sure about that.</p>
  
  <p>If in step (1), if I changed the date to de date to UTC before inserting it, <em>2014-04-15 <strong>10</strong>:00:00</em> will become <em>2014-04-15 <strong>08</strong>:00:00</em> and the output will be <em>2014-04-15 <strong>09</strong>:00:00</em>, which is still not correct.</p>
  
  <p>I highly suspect that the trick here is between steps (1) and (2), that is to say, me not being able to understand how Cassandra treats dates.</p>
  
  <hr>
  
  <p><strong>Edit1:</strong></p>
  
  <p>@Ananth 's questions:</p>
  
  <blockquote>
    <p>both cassandra and client run in the same datacenter?</p>
  </blockquote>
  
  <p>It is complicated:</p>
  
  <ul>
  <li>Insertion in .NET from server1, a different server from server-cassandra (datacenter).</li>
  <li>PHP (to pull the data) running on server-cassandra.</li>
  <li>.NET (to pull the data) running on server1, not on server-cassandra.</li>
  <li>PHP and .NET pulling the same result.</li>
  </ul>
  
  <blockquote>
    <p>Can you post your schema here?</p>
  </blockquote>
  
  <p>Here it is</p>
  
  <pre><code>CREATE TABLE tsmeasures (
    id int PRIMARY KEY
  ) WITH
    comment='' AND
    comparator=timestamp AND
    read_repair_chance=0.100000 AND
    gc_grace_seconds=0 AND
    default_validation=double AND
    min_compaction_threshold=4 AND
    max_compaction_threshold=32 AND
    replicate_on_write='true' AND
    compaction_strategy_class='SizeTieredCompactionStrategy' AND
    compression_parameters:sstable_compression='SnappyCompressor';
  </code></pre>
  
  <p><strong>Edit2:</strong></p>
  
  <p>After testing it step by step, this is the result:</p>
  
  <ul>
  <li>real date : 2014-04-15 17:00:00 (localtime)</li>
  <li>cql text : '2014-04-15 15:00:00' (to UTC, done through .NET)</li>
  <li>PHP Cassandra Unpack of this date => $ticks = 1397577600 (*) The unpack is done with the piece of code shown before</li>
  </ul>
  
  <p>Ticks converted (through <a href="http://www.epochconverter.com/" rel="nofollow">http://www.epochconverter.com/</a> )</p>
  
  <ul>
  <li>GMT: Tue,</li>
  <li>15 Apr 2014 16:00:00 GMT Your time zone: 4/15/2014 6:00:00 PM GMT+2</li>
  </ul>
  
  <p>These results makes no sense to me...</p>
  
  <p>More details:</p>
  
  <p>cql insert:</p>
  
  <blockquote>
    <p>"BEGIN BATCH USING CONSISTENCY ONE insert into tsmeasures(id,'2014-04-11 15:00:00',...,'2014-04-15 15:00:00') values ('2036548',0,...,4.85) APPLY BATCH"</p>
  </blockquote>
  
  <p>cql fetch:</p>
  
  <blockquote>
    <p>"SELECT '2014-04-10 16:00:00'..'2014-04-20 17:00:00' FROM tsmeasures WHERE id IN
    (2036548,2036479,2036174,650877)"</p>
  </blockquote>
  
  <p>Thus '2014-04-15 15:00:00' is included in the range of the fetch, and I can identify it because it is the highest value.</p>
  
  <p>I will keep digging...</p>