boost ptime from sql server timestamp_t is off by minutes. What did I do wrong?

Question

I have a SQL server database and I am pulling dates from it and converting the type of timestamp_t into Int64 as such:

Int64 from_timestamp_t(dtl::timestamp_t& t)
{
    // create a new posix time structure
    boost::posix_time::ptime pt
    (
    boost::gregorian::date           ( t.year, t.month, t.day),
    boost::posix_time::time_duration ( t.hour, t.minute, t.second, t.fraction )
    );

    ptime epoch(date(1970, Jan, 1));
    boost::posix_time::time_duration fromEpoch = pt - epoch;

    // return it to caller
    return fromEpoch.total_milliseconds();
}

I attempt to convert back to a boost ptime from an Int64 as such:

ptime from_epoch_ticks(Int64 ticksFromEpoch)
{
    ptime epoch(date(1970, Jan, 1), time_duration(0,0,0));
    ptime time = epoch + boost::posix_time::milliseconds(ticksFromEpoch);

    return time;
}

For some reason, and I can't figure out why, my dates, hours, etc are all correct, but my minutes are ahead a few minutes from what they should be. Is it because timestamps from the database are in seconds resolution and I'm using milliseconds? How do I fix this?

Applying the following modification as Dan suggested seems to have fixed the problem:

Int64 from_timestamp_t(dtl::timestamp_t& t)
{
    int count = t.fraction * (time_duration::ticks_per_second() % 1000);

    boost::posix_time::ptime pt
        (
        boost::gregorian::date           ( t.year, t.month, t.day ),
        boost::posix_time::time_duration ( t.hour, t.minute, t.second, count )
        );

    ptime epoch(date(1970, Jan, 1), time_duration(0, 0, 0, 0));

    boost::posix_time::time_duration fromEpoch = pt - epoch;

    return fromEpoch.total_milliseconds();
}

Dan's answer did the trick, I modified my solution above – jjacksonRIAB Sep 14 '11 at 22:34 — jjacksonRIAB, Sep 14 '11 at 22:34

score 1 · Accepted Answer · answered Sep 14 '11 at 12:27

I'm not familiar with SQL Server 2005, but boost posix time has the seconds function if ticksFromEpoch is equivalent to one second.

ptime time = epoch + boost::posix_time::seconds(ticksFromEpoch);

However, the generic way to handle this is presented in the boost date_time documentation:

Another way to handle this is to utilize the ticks_per_second() method of time_duration to write code that is portable no matter how the library is compiled. The general equation for calculating a resolution independent count is as follows:

count*(time_duration_ticks_per_second / count_ticks_per_second)

For example, let's suppose we want to construct using a count that represents tenths of a second. That is, each tick is 0.1 second.

int number_of_tenths = 5; // create a resolution independent count -- 
                          // divide by 10 since there are
                          //10 tenths in a second.
int count = number_of_tenths*(time_duration::ticks_per_second()/10);
time_duration td(1,2,3,count); //01:02:03.5 //no matter the resolution settings

I find that if I convert to a time_t first and then to a ptime using from_time_t then return total_milliseconds as before it comes out correctly. There has to be something wrong with constructing my posix_time from gregorian and time_duration, but I still don't see it. — jjacksonRIAB, Sep 14 '11 at 22:07

boost ptime from sql server timestamp_t is off by minutes. What did I do wrong?

1 Answers1