Interpreting mysql connector c++ field Value's raw 4 bytes to a date

Question

In the current database, I select all from a pet table, there is exactly 1 record and it has two DATE field, one for the birth date and one for the death date.

My record has a value for the birth date and a NULL for the death date.

Header files

#include <iostream>
#include <mysqlx/xdevapi.h>

using directives

using ::std::cout;
using ::std::endl;
using namespace ::mysqlx;

First I connect the session

Session sess("<session uri>");

Then I output some information about the table

std::string schema_name = sess.getDefaultSchemaName();
cout << "schema_name : " << schema_name << endl;

Schema db_schema = sess.getSchema( schema_name, true );
std::string table_name { "pet" };

Table pet_table = db_schema.getTable( table_name, true );
cout << "table " << table_name << " has " << pet_table.count() << " row(s). is_view=" << std::boolalpha << pet_table.isView() << std::endl;

Finally I prepare my query

std::stringstream  query_writer;
query_writer << "SELECT * FROM " << table_name;
std::string query_str = query_writer.str();

SqlStatement query = sess.sql(query_str);

Then get my results:

SqlResult res = query.execute();
Row pet;
while( (pet = res.fetchOne()) ) {
    int count = res.getColumnCount();
    for( int column = 0; column < count; column++ ) {
        const Column& current_column = res.getColumn( column );
        const Value& field_value = pet[column];
        const std::string& field_name = current_column.getColumnName();
        Type field_type= current_column.getType();

        // HOW to Interpret this?
        const bytes& raw_bytes = field_value.getRawBytes();
    }
}

It was pointed out to me that DATE fields encoding are described in this link:

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

but non-null DATE field raw_bytes is 4 bytes long, not 3 bytes long, so where is the correct documentation?

Originally, the table had a DATE field, now it is changed for a DATETIME field:

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | datetime    | YES  |     | NULL    |       |
| death   | datetime    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Here are the records in the tabe:

mysql> select * from pet;
+----------+-------+---------+------+---------------------+-------+
| name     | owner | species | sex  | birth               | death |
+----------+-------+---------+------+---------------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 00:00:00 | NULL  |
+----------+-------+---------+------+---------------------+-------+
1 row in set (0.00 sec)

Answer 1

We had the same issue and developed the following C++20 helper methods for production use with mysqlx (MySQL Connector/C++ 8.0 X DevAPI) to properly read DATE, DATETIME and TIMESTAMP fields:

#pragma once

#include <vector>
#include <cstddef>
#include <chrono>
#include <mysqlx/xdevapi.h>

namespace mysqlx {

static inline std::vector<uint64_t>
mysqlx_raw_as_u64_vector(const mysqlx::Value& in_value)
{
  std::vector<uint64_t> out;

  const auto bytes = in_value.getRawBytes();
  auto ptr = reinterpret_cast<const std::byte*>(bytes.first);
  auto end = reinterpret_cast<const std::byte*>(bytes.first) + bytes.second;

  while (ptr != end) {
    static constexpr std::byte carry_flag{0b1000'0000};
    static constexpr std::byte value_mask{0b0111'1111};

    uint64_t v = 0;
    uint64_t shift = 0;
    bool is_carry;
    do {
      auto byte = *ptr;
      is_carry = (byte & carry_flag) == carry_flag;
      v |= std::to_integer<uint64_t>(byte & value_mask) << shift;

      ++ptr;
      shift += 7;
    } while (is_carry && ptr != end && shift <= 63);

    out.push_back(v);
  }

  return out;
}

static inline std::chrono::year_month_day
read_date(const mysqlx::Value& value)
{
  const auto vector = mysqlx_raw_as_u64_vector(value);
  if (vector.size() < 3)
    throw std::out_of_range{"Value is not a valid DATE"};

  return std::chrono::year{static_cast<int>(vector.at(0))} / static_cast<int>(vector.at(1)) / static_cast<int>(vector.at(2));
}

static inline std::chrono::system_clock::time_point
read_date_time(const mysqlx::Value& value)
{
  const auto vector = mysqlx_raw_as_u64_vector(value);
  if (vector.size() < 3)
    throw std::out_of_range{"Value is not a valid DATETIME"};

  auto ymd = std::chrono::year{static_cast<int>(vector.at(0))} / static_cast<int>(vector.at(1)) / static_cast<int>(vector.at(2));
  auto sys_days = std::chrono::sys_days{ymd};

  auto out = std::chrono::system_clock::time_point(sys_days);

  auto it = vector.begin() + 2;
  auto end = vector.end();

  if (++it == end)
    return out;
  out += std::chrono::hours{*it};

  if (++it == end)
    return out;
  out += std::chrono::minutes{*it};

  if (++it == end)
    return out;
  out += std::chrono::seconds{*it};

  if (++it == end)
    return out;
  out += std::chrono::microseconds{*it};

  return out;
}

} //namespace

Which can then be used as follows:

auto row = table.select("datetime", "date").execute().fetchOne();
auto time_point = read_date_time(row[0]);
auto year_month_day = read_date(row[1]);

Answer 2

I've read your question and the whole discussion on dev.mysql before making my own topic because I had exactly the same question. It's been a while since you asked this, but here is the answer: How is date encoded/stored in MySQL?

In a nutshell, the last two bytes are the month and the day respectively, and the first two encode the year so that you can calculate them using the following equation:

N1 - 128 + N2 * 128

It seems that the logic of encoding is to erase the most significant bit of the first number and to write the second number from this erased bit like this:

2002 from 210 and 15:

1101 0010 -> _101 0010;
0000 1111 + _101 0010 -> 0111 1101 0010

2048 from 128 and 16:

1000 0000 -> _000 0000
0001 0000 + _000 0000 -> 1000 0000 0000

From here (getBytes() part) https://dev.mysql.com/doc/dev/connector-cpp/8.0/classmysqlx_1_1abi2_1_1r0_1_1_row.html I've found out that:

1. The raw bytes are as received from the server. In genral the value is represented using x-protocol encoding that corresponds to the type and other meta-data of the given column. This meta-data can be accessed via Column object returned by RowResult#getColumn() method.
2. Values of type DATE and TIMESTAMP use the same representation as DATETIME, with time part empty in case of DATE values.