recommended format to save time with MJD + BCD format in database

Question

Hi,

There is a time represented in MJD and BCD format with 5 bytes .I am wondering what is the recommended format to save this date-time in the sqlite database so that user can search against it ?

My first attempt is to save it just as it is, that is a 5 bytes string. The user will use the same format to search and the result will be converted to unix time by the user with following code.

However, later, I was suggested to save the time in the integer - the UTC time, for example. But I can not find a standard way to do the conversion.

I feel this is a common issue and would like to hear your comments.

time_t sidate_to_unixtime(unsigned char sidate[])
{
int k = 0;
struct tm tm;
double mjd;

/* check for the undefined value */
if ((sidate[0] == 0xff) &&
    (sidate[1] == 0xff) &&
    (sidate[2] == 0xff) &&
    (sidate[3] == 0xff) &&
    (sidate[4] == 0xff)) {
    return -1;
}

memset(&tm, 0, sizeof(tm));
mjd = (sidate[0] << 8) | sidate[1];

tm.tm_year = (int) ((mjd - 15078.2) / 365.25);
tm.tm_mon = (int) (((mjd - 14956.1) - (int) (tm.tm_year * 365.25)) / 30.6001);
tm.tm_mday = (int) mjd - 14956 - (int) (tm.tm_year * 365.25) - (int) (tm.tm_mon * 30.6001);
if ((tm.tm_mon == 14) || (tm.tm_mon == 15)) k = 1;
tm.tm_year += k;
tm.tm_mon = tm.tm_mon - 2 - k * 12;

    tm.tm_sec = bcd_to_integer(sidate[4]);
tm.tm_min = bcd_to_integer(sidate[3]);
tm.tm_hour = bcd_to_integer(sidate[2]);

return mktime(&tm);
 }

Answer 1

Use the SQLite date and time functions.

For example,

datetime(MJD + 2400000.5)

test:

sqlite> select datetime(49987 + 2400000.5);
1995-09-27 00:00:00