Question on how to convert empty std::tm to timestamp and convert back in a MySQL database

[asmwarrior]

Hi, I have a MySQL database and I use soci library to access it.

There is a table, which has a "timestamp" column. In the associated C++ user defined class, I have a std::tm for this column.

Here is a demo code I used to convert the soci row to the user define class and convert back.

template<>
struct type_conversion<MyClass>
{
    typedef values base_type;

    static void from_base(const values& v, indicator ind, MyClass& row)
    {
        row.id = v.get<int>("id", -1);
        row.timestamp = v.get<std::tm>("timestamp", std::tm());
    }

    static void to_base(const MyClass& row, values& v, indicator& ind)
    {
        v.set("id", row.id);
        v.set("timestamp", row.timestamp);

        ind = i_ok;
    }
};

I see that if the timestamp column of the record is "NULL", then I got the result that every members of the std::tm is 0.

I think this is correct, because I have such line row.timestamp = v.get<std::tm>("timestamp", std::tm());, and the last argument std::tm() is the default value if the row has the NULL for this field.

But when I try to run the to_base function, I got an error if I have such code:

MyClass obj;
obj.timestamp = std::tm();
// later I try to add the obj to the database

The error says that: (I strip the error message, because the actual class name is not "MyClass")

can't find the MyClass with id = 8, Incorrect datetime value: '1900-01-00 00:00:00' for column 'timestamp' ...... 

So, it looks like I can't convert/write an empty std::tm object which as all the members as 0s to the database.

My question is how to solve such issue?

My guess is that I see the default value of the std::tm object(all members are zero), I would set the timestamp filed as a NULL.

So, some code may be changed to

    static void to_base(const MyClass& row, values& v, indicator& ind)
    {
        v.set("id", row.id);
        
        // check to see whether the row.timestamp is an empty null "std::tm", if true, set the field value as NULL
        if (row.timestamp == std::tm())
        {
            v.set("timestamp", soci::i_null);
        }
        else
        {
            v.set("timestamp", row.timestamp);
        }

        ind = i_ok;
    }

Am I correct?

Thanks.

[asmwarrior]

It looks like I can't compare the if (row.timestamp == std::tm()), so I change the code like below:

bool IsTmEmpty(const std::tm& time) {
    return (time.tm_year == 0 &&
            time.tm_mon == 0 &&
            time.tm_mday == 0 &&
            time.tm_hour == 0 &&
            time.tm_min == 0 &&
            time.tm_sec == 0);
}

And later, I use such code:

    static void to_base(const MyClass& row, values& v, indicator& ind)
    {
        v.set("id", row.id);
        
        // check to see whether the row.timestamp is an empty null "std::tm", if true, set the field value as NULL
        if ( IsTmEmpty(row.timestamp) )
        {
            v.set("timestamp", soci::i_null);
        }
        else
        {
            v.set("timestamp", row.timestamp);
        }

        ind = i_ok;
    }

But sadly, the above code still can't be built. The issue happens here: v.set("timestamp", soci::i_null);, I'm currently don't know how to use the null indicator.

Thanks.

[asmwarrior]

OK, I think I found the solution, it is inside the document:

See here:

https://github.com/SOCI/soci/blob/884808c294e4809064650f3e5aed9102d8a68d56/docs/types.md?plain=1#L303C1-L303C73

So, the final code which works is like below:

    static void to_base(const MyClass& row, values& v, indicator& ind)
    {
        v.set("id", row.id);

        // check to see whether the row.timestamp is an empty null "std::tm", if true, set the field value as NULL
        v.set("timestamp", row.timestamp, IsTmEmpty(row.timestamp) ? i_null : i_ok);

        ind = i_ok;
    }