Boost C++ Libraries

The date, datetime and time provide support for MySQL's native date and time types. This section expands on how to use them.

The date type

date represents MySQL DATE in C++. date holds the year, month and day components of a date. It is a type close to the protocol, rather than a vocabulary type. The main reason for using date instead of a std::chrono::time_point type is that, under certain configurations, MySQL allows storing invalid dates, such as 2020-00-01. These are not representable as a std::chrono::time_point.

Unless dealing with these special values, we recommend converting dates to a time_point before using them. The member type date::time_point is a system-clock time_point that can represent the entire MySQL DATE range without problems. You can use date::as_time_point to perform the cast:

date d(2020, 2, 19);                      // d holds "2020-02-19"
date::time_point tp = d.as_time_point();  // now use tp normally

If the date is not valid, as_time_point will throw an exception.

You can query whether a date contains a valid date or not using date::valid:

date d1(2020, 2, 19);  // regular date
bool v1 = d1.valid();  // true
date d2(2020, 0, 19);  // invalid date
bool v2 = d2.valid();  // false

You can combine it with date::get_time_point, which performs an unchecked conversion:

date d = /* obtain a date somehow */ date(2020, 2, 29);
if (d.valid())
{
    // Same as as_time_point, but doesn't check for validity
    // Caution: be sure to check for validity.
    // If d is not valid, get_time_point results in undefined behavior
    date::time_point tp = d.get_time_point();

    // Use tp as required
    std::cout << tp.time_since_epoch().count() << std::endl;
}
else
{
    // the date is invalid
    std::cout << "Invalid date" << std::endl;
}

You can also construct a date from a time_point. If the time_point is out of range, an exception is thrown.

The datetime type

datetime represents MySQL DATETIME and TIMESTAMP in C++. datetime represents a broken time point, having year, month, day, hour, minute, second and microseconds.

MySQL also accepts invalid datetimes (like 2020-00-10 10:20:59.000000). The same considerations for date apply:

datetime dt1(2020, 10, 11, 10, 20, 59, 123456);  // regular datetime 2020-10-11 10:20:59.123456
bool v1 = dt1.valid();                           // true
datetime dt2(2020, 0, 11, 10, 20, 59);           // invalid datetime 2020-00-10 10:20:59.000000
bool v2 = dt2.valid();                           // false

datetime::time_point tp = dt1.as_time_point();  // convert to time_point

The datetime object doesn't carry any time zone information with it. The time zone semantics depend on the actual MySQL type:

When using TIMESTAMP, we recommend setting the time_zone to a known value. To illustrate this, consider an event-logging system with the following table definition:

results result;
conn.execute(
    R"%(
        CREATE TEMPORARY TABLE events (
            id INT PRIMARY KEY AUTO_INCREMENT,
            t TIMESTAMP,
            contents VARCHAR(256)
        )
    )%",
    result
);

We will be inserting events with an explicit timestamp. We may also want to retrieve events with a timestamp filter. This is what our prepared statements would look like:

auto insert_stmt = conn.prepare_statement("INSERT INTO events (t, contents) VALUES (?, ?)");
auto select_stmt = conn.prepare_statement("SELECT id, t, contents FROM events WHERE t > ?");

These statements may be run from different parts of our code, or even from different applications. To get consistent results, we must make sure that the time zones used during insertion and retrieval are the same. By default, time_zone gets set to SYSTEM, which will use the server's time zone settings. This is not what we want here, so let's change it:

// This change has session scope. All operations after this query
// will now use UTC for TIMESTAMPs. Other sessions will not see the change.
// If you need to reconnect the connection, you need to run this again.
// If your MySQL server supports named time zones, you can also use
// "SET time_zone = 'UTC'"
conn.execute("SET time_zone = '+00:00'", result);

With this, the insertion code can look like:

// Get the timestamp of the event. This may have been provided by an external system
// For the sake of example, we will use the current timestamp
datetime event_timestamp = datetime::now();

// event_timestamp will be interpreted as UTC if you have run SET time_zone
conn.execute(insert_stmt.bind(event_timestamp, "Something happened"), result);

The querying code would be:

// Get the timestamp threshold from the user. We will use a constant for the sake of example
datetime threshold = datetime(2022, 1, 1);  // get events that happened after 2022-01-01

// threshold will be interpreted as UTC. The retrieved events will have their
// `t` column in UTC
conn.execute(select_stmt.bind(threshold), result);

If you don't set time_zone, you may apparently get the right results if you run both insertions and queries from clients that don't set time_zone and the server doesn't change its configuration. However, relying on this will make your applications brittle, so we don't recommend it.

The TIME type

The TIME type is a signed duration with a resolution of one microsecond. It is represented using the time type, an alias for a std::chrono::duration specialization with microseconds as period.