March 31, 2010
Time Out!
Time and date handling has always been a challenging task for developers. With few standards available, the choices become important in cross-platform applications.
c‑tree Date Epochs
c‑treeACE SQL Date columns are stored as 32-bit unsigned integers with the value representing the number of days since 28 Feb 1700 (day 0). Within c‑treeACE this represented in the DODA resource as a CT_DATE type. A description of why this epoch was chosen is given later in this article.
c‑treeACE SQL Time columns (CT_TIME) are also a 32-bit unsigned integer with the value representing the number of seconds since midnight. (There are 86,400 seconds in a day.)
As both of these types are simple integer values, it is very easy to add and subtract dates or times directly from their respective types. Notice that negative values do not have meaning in these representations.
Combining these values into a single CTDATETIME, a timestamp value, is stored as a CT_TIMES type in the DODA as a double value. The value on the left side of the decimal is the date component, that same value as a CTDTATE field would represent. The value to the right of the decimal represents the time, and is a bit more interesting. It is the value of what a CTTIME would represent as a fraction of the total seconds in a day. That is, it is CTTIME / 86,400. (As a division, there is handling of minor roundoff errors involved.)
Note that the core c‑tree API itself does not have any native date or time handling functions nor does it require any interpretation of what the values are. The CT_DATE, CT_TIME and CT_TIMES field types are solely dependent upon the implemented application’s interpretation of the values.
Current Time and Date
Requests have been made for retrieving the current time and date. While the traditional c‑tree API doesn’t provide native date and time functions, the c‑treeDB C and C++ APIs support these useful activities:
- ctdbCurrentDate()
- ctdbCurrentTime()
- ctdbCurrentDateTime()
Note that these will return a time from the CLIENT application machine and not centralized server times. To have a central time assigned by the server, consider the use of the c‑treeACE SQL NOW() scalar function. The customCTUSER() function is also frequently pressed into service to return the server time to an ISAM client application.
Legacy Time Values
Many existing c‑tree applications continue to use date formats specific to the application. These may or may not correspond to c‑treeACE SQL date types. When importing this existing data into c‑treeACE SQL unexpected results can happen if these dates are not interpreted correctly. For example, consider a Unix application that store the date as a Unix time_t value using the 1 Jan 1970 epoch. These dates require conversion to c‑tree compatible dates before using with c‑treeACE SQL.
The c‑treeACE SQL Types SDK makes this possible. Implementing a dedicated callback module, existing dates can be converted on-the-fly to c‑treeACE SQL and back as needed. While imposing only a minor performance penalty, this allows valuable existing application data to take advantage of SQL reporting capabilities. Contact FairCom if you have unique field types not corresponding to the available SQL types.
.NET DateTime vs TimeSpan
The c‑treeACE SQL ADO.NET Data Provider currently returns a TIME column as a .NET CLR DateTime object. This was because a DateTime type best represented a specific point in time, and there is no Time type in the .NET CLR for such an entity. However, Microsoft had previously mapped ODBC .NET TIME types as TimeSpan. The c‑treeACE SQL Data Provider has since been updated as of V9.3 to a TimeSpan mapping for the easiest Microsoft compatibility. Please be aware of this change if you are currently utilizing the DateTime object and you later upgrade.
Common Date Epochs
Consider these common date epochs:
- c‑tree (March 1 1700)
- Windows .NET CLR (Jan 1 1601)
- MS Excel (Jan 1 1900)
- Unix (Jan 1 1970)
Why did FairCom choose such a unique date for our epoch? 1 March 1700 is the first date that is not impacted by the adoption of the Gregorian calendar AND avoids the leap year calculation imposed by that year. Also, c‑tree database technology has been around since 1979 — nearly as long as Unix. Few universal standards existed at the time this convention was chosen, and certainly long before MS Excel and .NET made their appearance.
Year 1700
Year 1700 (MDCC) was an exceptional common year starting on Friday of the Gregorian calendar, but a leap year starting on Monday [1] of the Julian calendar. The Gregorian calendar was 10 days ahead of the Julian calendar until February 28 (O.S. February 18, 1700) 1700, then 11 days ahead since March 1 (O.S. February 19, 1700) 1700. With the Gregorian Calendar starting in 1582, and 1600 being evenly divisible by 400, and thus being a leap year under the Gregorian system despite being a century year, this was the first skipped leap year under the Gregorian Calendar.
The US officially adopted the Gregorian calendar Sept 14, 1752.
http://en.wikipedia.org/wiki/1700
http://www.searchforancestors.com/utility/gregorian.html
The 1900 Date System
In the 1900 date system, the first day that is supported is January 1, 1900. By default, Microsoft Excel for Windows and Microsoft Excel for Windows NT use the 1900 date system. The 1900 date system allows greater compatibility between Microsoft Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows. PC Excel day numbers are valid only between 1 (January 1, 1900) and 2958465 (December 31, 9999).
http://www.fourmilab.ch/documents/calendar/
The 1904 Date System
In the 1904 date system, the first day that is supported is January 1, 1904. By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported; this design was intended to prevent problems related to the fact that 1900 was not a leap year. Note that if you switch to the 1900 date system, Microsoft Excel for the Macintosh does support dates as early as January 1, 1900. The difference between the two date systems is 1,462 days
Unix Time
Unix time, or POSIX time, is a system for describing points in time, defined as the number of seconds elapsed since midnight proleptic Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds. It is used widely, not only in Unix-like operating systems, but also in many other computing systems and file formats.
Example
time_t now;
struct tm *ts;
char buf[80];
/* Get the current time */
now = time(NULL);
/* Format and print the time, "ddd yyyy-mm-dd hh:mm:ss zzz" */
ts = localtime(&now);
strftime(buf, sizeof(buf), "%a %Y-%m-%d %H:%M:%S %Z", ts);
printf("%s
", buf);
http://en.wikipedia.org/wiki/Unix_time
Y2038
On January 19 2038, Unix time 2147483647, systems still storing Unix time as a signed 32-bit integer will start to malfunction due to an overflow.
Stay tuned for future updates regarding c‑treeACE Y2038 compliance, as this will be further addressed in the very near future.
http://en.wikipedia.org/wiki/Year_2038_problem
Other Interesting Date and Time Facts
- Earth’s rotation is constantly slowing. As a result a few “leap” seconds are added periodically to compensate.
- A ‘jiffy’ is an actual unit of time for 1/100th of a second.
- A “moment” is equal to one minute and thirty seconds.
- England and the American colonies adopted the Gregorian calendar on September 14th, 1752. 11 days disappeared.
- The wristwatch was invented in 1904 by Louis Cartier.
- The Romans constructed their first sundial in 164 B.C.
- On September 9, 2001, at exactly 01:46:40 (UTC) the decimal representation of Unix time was equal to 1000000000.
- On February 13, 2009, at exactly 23:31:30 (UTC) the decimal representation of Unix time was equal to 1234567890.