PostgreSQL binary timestamp functions

While writing libpq wrapper I've run into problems with binary timestamps, which required a lot of debugging and headaches. To avoid your own migraine, feel free to use my code below. Note, however, this code is not perfect, as it's uses long long for timestamps. This will work if your machine is 32bit and your server was compiled with `--enable-integer-datetimes` (highly recommended). If not, You're welcome to patch this code and post it here, so other people will thank you.

bool Database::get(int row_number, int column_number, struct tm *value)
    long long timestamp;
    // get long long with correct byte ordering
    if(!get(row_number, column_number, ×tamp))
        return false;

    memset(value, 0, sizeof(struct tm));

    long long dDate, date0, time;

    date0 = date2j(2000, 1, 1);

    time = timestamp;
    TMODULO(time, dDate, USECS_PER_DAY);

    if(time < (long long)0) {
        time += USECS_PER_DAY;
        dDate -= 1;

    // add offset to go from J2000 back to standard Julian date
    dDate += date0;

    // Julian day routine does not work for negative Julian days 
    if(dDate < 0 || dDate > (long long)INT_MAX)
        return false;

    j2date ((int)dDate, &value->tm_year, &value->tm_mon, &value->tm_mday);
    dt2time(     time,  &value->tm_hour, &value->tm_min, &value->tm_sec);

    // years in tm starts from 1900
    value->tm_year -= 1900;
    // months in tm are zero based

    // mark this as *no* time zone available
    value->tm_isdst = -1;

    return true;

bool Database::put(struct tm* value)
    // Julian day routines are not correct for negative Julian days
    if(!IS_VALID_JULIAN(value->tm_year + 1900, value->tm_mon + 1, value->tm_mday))
        return false;

    long long dDate, time, timestamp;

    dDate = date2j(value->tm_year + 1900, value->tm_mon + 1, value->tm_mday) -
            date2j(2000, 1, 1);
    time  = time2t(value->tm_hour, value->tm_min, value->tm_sec);

    timestamp = dDate * USECS_PER_DAY + time;

    // check for major overflow
    if((timestamp - time) / USECS_PER_DAY != dDate)
        return false;
    // check for just-barely overflow (okay except time-of-day wraps)
    if((timestamp  < 0 && dDate >= 0) ||
       (timestamp >= 0 && dDate <  0))
        return false;

    long long *ts = ×tamp;
    unsigned long l1 = htonl(*((unsigned long *) ts + 0));
    unsigned long l2 = htonl(*((unsigned long *) ts + 1));
    unsigned long long ll = l1;
    ll = (ll << 32) + l2;

    return put(OID_DATETIME, (char*) &ll, sizeof(long long));

All the functions from this code are available in postgres sources, except `put` and `get` which I will not publish due to specifics of my code. If you're using this, you should already have your own functions for argument saving into some buffer.

No comments:

Post a Comment