How-to Guides‎ > ‎

Setting and retrieving datetime attributes

Polyhedra supports an 8-byte datetime attribute type, which holds a date and time with microsecond resolution.  The zero point for such values is midnight on the 1st of January 1 AD, in the Julian calendar, but the Gregorian calendar is used when translating all dates after Wednesday 2 September 1752 - which is the day that Britain and its then colonies switched from the Julian to Gregorian calendar.

Polyhedra supports arithmetic on datetime values (which is performed in the 'obvious' way, without any consideration of leap seconds) which allows them to be treated as intervals, and supports a large number of functions for converting between datetime and other types. For example, Polyhedra's SQL engine allows you to say

select to_minutes(date('01-Jan-1970')) epoch_offset from tables where name='tables'

... which yields the value 1035596160, the number of minutes between the zero-point of the Polyhedra datetime type and the Unix Epoch (the zero point of Unix time or POSIX time, 00:00:00 UTC on 1 January 1970). Note that Polyhedra adopts the current British convention that the beginning of each year is the 1st of January even for those dates where a different convention may have been in place. (For some time, for example, the 1st day of the English year was 25 March, so 24 March 1616 would have been followed by 25 March 1617! For Polyhedra, though, 31 December 1615 is followed by 1 January 1616, and 24 March 1616 by 25 March 1616.)

Data input and output via a structure defined by ODBC

It is cumbersome and inefficient to convert all time stamps to string form in order to enter values in the database, so Polyhedra allows ODBC applications to transfer values via SQL_TIMESTAMP_STRUCT structures. If running on Windows, you can initialise a SQL_TIMESTAMP_STRUCT to the current date and time with the following code snippet:

SQL_TIMESTAMP_STRUCT tsvalue;
SYSTEMTIME           tv;

GetSystemTime(&tv);

tsvalue.year     = tv.wYear;
tsvalue.month    = tv.wMonth;
tsvalue.day      = tv.wDay;
tsvalue.hour     = tv.wHour;
tsvalue.minute   = tv.wMinute;
tsvalue.second   = tv.wSecond;
tsvalue.fraction = tv.wMilliseconds * 1000000; /* convert milliseconds to nanoseconds */

... whereas in POSIX-compliant systems the following snippet fulfils a similar purpose:

SQL_TIMESTAMP_STRUCT tsvalue;
timeval              tv;
tm                   broken_down_time;

gettimeofday(&tv, 0);
gmtime_r (&(tv.tv_sec), &broken_down_time);

tsvalue.year     = broken_down_time.tm_year;
tsvalue.month    = broken_down_time.tm_mon;
tsvalue.day      = broken_down_time.tm_mday;
tsvalue.hour     = broken_down_time.tm_hour;
tsvalue.minute   = broken_down_time.tm_min;
tsvalue.second   = broken_down_time.tm_sec;
tsvalue.fraction = tv.tv_usec * 1000;      /* convert microseconds to nanoseconds */

(All the code snippets in this note are adapted from a full working example, a copy of which can be downloaded from the link at the bottom of this page.)

Having set up a SQL_TIMESTAMP_STRUCT structure, you can bind it to a parameter of an SQL string, and then execute it. Thus, if there is a table called test with an integer primary key and a data field of type datetime, and the variables hdbc, id and tsvalue hold, respectively the the handle for an open connection, the id of a record (in an SQLINTEGER) and a timestamp (in a SQL_TIMESTAMP_STRUCT), then the following code will update the timestamp in the indicated record (or create it with the appropriate values if it does not already exist):

SQLHANDLE  hstmt;
SQLRETURN  ret = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
if (ret == SQL_SUCCESS)
{
    SQLINTEGER id = 1;
    SQLLEN     indicators[2];
    indicators[0] = 0;

    indicators[1] = 0;
    ret = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
                            &id, sizeof(id), &indicators[0]);
    if (ret == SQL_SUCCESS)
       ret = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TIMESTAMP, 0, 0,
                               &tsvalue, sizeof(tsvalue), &indicators[1]);
    if (ret == SQL_SUCCESS)
       ret = SQLExecDirect (hstmt, (SQLCHAR*)"insert or update test values (?,?)", SQL_NTS);
    SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
}

It is also possible to retrieve the value from a datetime field into a SQL_TIMESTAMP_STRUCT.

Handling Unix time values

In many applications, it is convenient to use as a timestamp a Unix time (a number of seconds since the Unix Epoch, see above) plus a microsecond value. Clearly, such a timestamp can be stored in the database as a pair of integer fields, but it is often more sensible to use a datetime field: it is a better representation of the meaning of the timestamp, it allows them to be compared and shown in a variety of textual forms. The following code fragment shows how such a timestamp can be easily stored in the database, without having to be converted to a textual representation or even to a SQL_TIMESTAMP_STRUCT:
 
SQLRETURN WriteTime (SQLHANDLE hdbc, int id, long secs, long usecs)
{
    SQLHANDLE  hstmt;
    SQLRETURN  ret = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
    if (ret == SQL_SUCCESS)
    {
        SQLCHAR*   sql    = (SQLCHAR*)("insert or update test "
                                       "values (?,minutes(?)+microseconds(?))");
        SQLINTEGER ID     = id;
        SQLINTEGER mins   = 1035596160 + secs / 60;
        SQLINTEGER micros = usecs + 1000000 * (secs % 60);
        SQLLEN     indicators[3];
        indicators[0] = 0;

        indicators[1] = 0;
        indicators[2] = 0;

        ret = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
                                    &ID,  sizeof(ID),  &indicators[0]);
        if (ret == SQL_SUCCESS)
            ret = SQLBindParameter (hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
                                    &Mins,  sizeof(Mins),  &indicators[1]);
        if (ret == SQL_SUCCESS)
            ret = SQLBindParameter (hstmt, 3, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
                                    &Usecs, sizeof(Usecs), &indicators[2]);
        if (ret == SQL_SUCCESS)
            ret = SQLExecDirect (hstmt, sql, SQL_NTS);

        SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
    }   
    return ret;
}

The above function makes use of the constant 1035596160, which as we saw earlier corresponds to the start time of the Unix Epoch, in minutes. Retrieving a datetime value as a Unix Time + microsecond pair is also straightforward:

SQLRETURN ReadTime (SQLHANDLE hdbc, int id, long* sec_ptr, long* usec_ptr)
{
    SQLHANDLE  hstmt;
    SQLRETURN  ret = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
    if (ret == SQL_SUCCESS)
    {
        SQLINTEGER mins;
        SQLINTEGER secs;
        SQLCHAR*   sql = (SQLCHAR*) ("select to_minutes(data1), "
                                     "1000000*the_second(data1)+the_microsecond(data1) "
                                     "from test where id=?");
        SQLINTEGER ID = id;
        SQLLEN     indicators[3];
        indicators[0] = 0;

        ret = SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,
                                   &ID, sizeof(ID), &indicators[0]);
        if (ret == SQL_SUCCESS)
            ret = SQLBindCol (
hstmt, 1, SQL_INTEGER, &mins, sizeof(mins), &indicators[1]);
        if (ret == SQL_SUCCESS)
            ret = SQLBindCol (
hstmt, 2, SQL_INTEGER, &usecs, sizeof(usecs), &indicators[2]);
        if (ret == SQL_SUCCESS)
            ret = SQLExecDirect (
hstmt, sql, SQL_NTS);
        if (ret == SQL_SUCCESS)
            ret = SQLFetch (
hstmt);
        if (ret == SQL_SUCCESS)
        {
            *sec_ptr  = 60*(mins-1035596160) + usecs / 1000000;
            *usec_ptr =
usecs % 1000000;
        }
        SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
    }
    return ret;
}

Summary

as can be seen, it is pretty straightforward to use Polyhedra's datetime type, and there is usually no need to make use of string representations of a date and/or time when passing values between the client and server. Thus, where the datetime type is appropriate for storing your data, there is no need to look for alternative representations in the name of efficiency.

Appendix: now()

The following function provides a way of finding out the current time and date on Windows and Posix-compatible platforms, with reasonable efficiency. As well as setting the supplied locations to hold the seconds and microseconds since the Unix Epoch (midnight on 1 January 1970 UTC), it returns a long long representing the total number of microseconds since that time; this will only work on platforms where long long is big enough to hold such values!

(The Windows version make use of FILETIME, which has as its zero point midnight on January 1601 Gregorian, so to correct to Unix time we have to subtract 11644473600 seconds. If calculating this offset using Polyhedra's date conversions functions, one has to remember that Polyhedra tries (too hard?) to be clever, and allows for the adjustment between Julian and Gregorian calendars that was done (in the UK) in 1782; in 1601 the difference between the two calendars was 10 days.Thus, the calculation to determine the adjustment becomes: select 60*to_minutes(date('01-Jan-1970')-date('01-Jan-1601')+days(10)) adjust from tables where name='tables'.)
 

long long now (long* sec_ptr = 0, long* usec_ptr = 0)
//
// get the time since the start of the epoch (January 1970) in seconds and microseconds
//
{
    long secs, usecs;
#if defined(WIN32)
    FILETIME       ft;
    ULARGE_INTEGER ularge;
    GetSystemTimeAsFileTime(&ft);
    ularge.LowPart  = ft.dwLowDateTime;
    ularge.HighPart = ft.dwHighDateTime;
    secs            = (long) (((ularge.QuadPart) / 10000000L) - 11644473600L);
    usecs           = (long) (((ularge.QuadPart) % 10000000L) / 10L);
#else
    timeval tv;
    gettimeofday(&tv, 0);
    secs  = tv.tv_sec;
    usecs = tv.tv_usec;
#endif
    if (sec_ptr  != 0) *sec_ptr  = secs;
    if (usec_ptr != 0) *usec_ptr = usecs;
    return (long long)(secs)*1000000L + usecs;
}

ċ
datetime.zip
(14k)
Nigel Day,
6 Aug 2013, 13:44