Re: GWindows Database time type

[Fionn mac Cuimhaill <invisible@hiding.from.spam>]

On 29 Sep 2004 20:13:23 -0400, Stephen Leake <stephen_leake@acm.org>
wrote:

>Fionn mac Cuimhaill <invisible@hiding.from.spam> writes:
>
>> On another front - the proposed bug list. I have an item for it.
>> This might be described as a restriction rather than a bug; this is in
>> GWindows.Databases. I was using one of the Field_Value functions to
>> get a SQL Server datetime value into a variable of type Time (as
>> defined in Ada.Calendar).  The problem is that the range of dates that
>> can be expressed with a variable of type Time is too narrow, and one
>> of the GNATCOM routines which converts the date extracted from SQL
>> Server into a Time choked on a date with a year before 1901.
>> 
>> GNATCOM and GWindows need a calendar package similar to Ada.Calendar
>> which is based on a native Windows time representation.
>
>Native Windows might be the wrong choice; databases are hosted on many
>operating systems, and GWindows should be able to provide clients for
>any of them, possibly thru ODBC.
>
>Here's a fragment from the MySQL manual
>(http://dev.mysql.com/doc/mysql/en/TIME.html):
>
>MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
>'HHH:MM:SS' format for large hours values). TIME values may range from
>'-838:59:59' to '838:59:59'.
>
>For that, Time needs to be a string; that will always be the most
>general format.
>
It's difficult to do tate arithmetic on strings; Windows already has a
SYSTEMTIME structure that is used in GNATCOM; there is also a 64-bit
integer representation of time. According to Microsoft docs,
SYSTEMTIME accomodates years all the way back to 1601. I'm not sure
what the 64-bit integer represents. (milliseconds since Jan 1,
1601???)  I have no idea of the reason for the 1601 date; in any case,
it is a great improvement over the 1901 limiting date used in
Ada.Calendar. There is no reason why a GWindows.Calendar package
couldn't be created that makes use of the SYSTEMTIME structure and
also supports a date representation usable for date arithmetic. It
looks to me like David Botton went with Ada.Calendar because it was an
already existing standard package, which if not perfect, was at least
usable and saved him the time of writing a calendar package.
>I couldn't find my SQL reference, so I'm not sure if that's standard
>SQL or not. Anybody have a URL for a good SQL reference?
>
>This gets back to "What are the requirements for GWindows"? Should it
>meet some industry standard for SQL? or match MySQL, which is a
>popular Open Source database? Or something else?

The native Windows time formats certainly cover a wide enough range of
dates to work better than Ada.Calendar. There doesn't seem to be any
element of GWindows which actually parses SQL; SQL code is managed
entirely by the programmer, and therefore any dialect of SQL can be
used. It wouldn't be too difficult to create a GWindows.Calendar that
could handle dates from any kind of database.