Converting a SQLite julianday to a QDateTime

scarleton

Ok, I see the QDate::fromJulianDay( int jd ), but it takes an integer and the SQLite julianday is a double, having time, along with the date. How exactly does one go about converting the SQLite julianday into a complete QDateTime?

goetz

As sqlite does not have a dedicated date/time/datetime/timstamp type, you will have to resort to text (for ISO8601 strings), real (for julian day numbers with fractions) or integer (for unix seconds since epoch). You will have to convert any of those to a datetime type. For highest compatibility I would go with ISO8601 text strings or the integers for unix seconds. Reals for julian calendar dates is quit unusual from my experience.

If you really need to go with the reals, then you'll have to convert it into a integer for the day and calculate the time from the fraction: 86400 * fraction = seconds in day.

scarleton

Well, it is a bit late in things to convert from reals. So if I understand you correctly, I simply take the faction from the real, multiply it by 86400 and set that as the milliseconds on the QTime and I should be golden.

Thank you!

andre

Basically, yes, though precision is going to be off. I would round off the result of the multiplication to the nearest 1000, so you don't give a false impression of millisecond precision.

goetz

@scarleton:
That's the seconds (60 * 60 * 24 = 86400), for milliseconds multiply the result with 1000. But as André already stated, that would give you most probably a false precision.

goetz

Something to watch for: The julian dates start at noon (12:00 UTC) according to the "SQLite docs":http://www.sqlite.org/lang_datefunc.html.

You can use this helper function to convert a julian date/time double to a QDateTime:

@
#include <QtCore/qmath.h>
#include <QDate>

QDateTime julianDoubleToDateTime(double julian)
{
// The day number is the integer part of the date
int julianDays = qFloor(julian);
QDate d = QDate::fromJulianDay(julianDays);

// The fraction is the time of day
double julianMSecs = (julian - static_cast<double>(julianDays)) * 86400.0 * 1000;

// Julian days start at noon (12:00 UTC)
QTime t = QTime(12, 0, 0, 0).addMSecs(qRound(julianMSecs));

return QDateTime(d, t, Qt::UTC);

}
@

scarleton

Thank you, that works like a charm!

goetz

Thanks, you're welcome.

I've added a doc note to [[Doc:QDateTime]] for further reference.