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, without any time zone information. 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.

Since date represents a local time point, std::chrono::local_time is the most accurate representation for it. If your compiler supports C++20 calendar types (as per __cpp_lib_chrono >= 201907L), you can use date::as_local_time_point to perform the cast:

date d(2020, 2, 19);                                   // d holds "2020-02-19"
std::chrono::local_days tp = d.as_local_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_local_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
    std::chrono::local_days tp = d.get_local_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;
}

If your compiler doesn't support local times, you can use date::get_time_point or date::as_time_point, instead. These return date::time_point objects, which are time_points that use the system clock. These time points should be interpreted as local times, rather than UTC:

date d(2020, 2, 19);  // d holds "2020-02-19"

// date::time_point is a std::chrono::time_point that uses std::chrono::system_clock
// tp is a local time, rather than UTC
// tp holds the same time_since_epoch() than d.as_local_time_point()
date::time_point tp = d.as_time_point();

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 microsecond.

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

MySQL also accepts invalid datetimes (like 2020-00-10 10:20:59.000000).

As with date, you can use datetime::as_local_time_point, get_local_time_point and valid (or as_time_point and get_time_point, if your compiler doesn't support C++20 calendar types):

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


// local_time_point is a std::chrono::local_time with microsecond resolution
// Only available if your compiler supports C++20 calendar types
datetime::local_time_point tp = dt1.as_local_time_point();

// If you're using an older compiler, use as_time_point.
// tp2 uses std::chrono::system_clock and microsecond resolution.
// tp2 should be interpreted as a local time, rather than UTC
datetime::time_point tp2 = dt1.as_time_point();

TIMESTAMP considerations

When using TIMESTAMP, we recommend setting the time_zone session variable 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 UTC

// 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.