Nominate our 2022 Qt Champions!

Converting a SQLite julianday to a QDateTime

  • 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?

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

  • 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!

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

  • @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.

  • Something to watch for: The julian dates start at noon (12:00 UTC) according to the "SQLite docs":

    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);


  • Thank you, that works like a charm!

  • Thanks, you're welcome.

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

Log in to reply