recommended format to save time with MJD + BCD format in database
- by pierr
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);
}
