[yunqa.de] Re: TDISQLite3Database and DateTime format
- From: edoardo falzetti <edoardo.falzetti@xxxxxxxxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 25 Feb 2010 08:02:24 +0100
First of all thanks for the answer; doing the way you say it works.
I now change the format of the dates in the db from TDateTime to JulianDate and
gladly go on working.
Before that i'd like to let you know why i had chosen to use DateTime format
although i knew you were suggesting JulianDate format.
The points are not in order of importance.
1) Thinking to speed up a little (as far i understand TDataTime has the same
size and date range as JulianDate and obviuosly no need of conversion)
2) I work with the db only within my delphi applications and i do not need the
DISQLite3 Date Time formatting functions.
3) During debug is useful to check the correspondance between the values in the
db and in the program; obviously it is easier if their data formats are the
same.
4) I had found a sqlite3 manager which shows well formatted dates from TDateTime
fields (Bougth just for this reason)
5) In my program i already have to deal with 4 different date formats and i
preferred not to introduce a 5th.
6) As a (every?) Delphi programmer i prefer dealing with TDateTime format.
And at last some final suggestions. It would be useful to have:
a) A date column in the tables of your demos.
b) An option to tell TDISQLite3Database.sqlite3_cell16_asdatetime how to
interpret the Date Time fields.
c) An option to tell SQLIteSpy to show JulianDates as well formatted dates.
Many thanks again
Scrive Delphi Inspiration <delphi@xxxxxxxx>:
> At 10:21 10.02.2010, edoardo falzetti wrote:
>
> >I have a SQLite3 database on disk where dates are in TDataTime format stored
> as float numbers.
>
> Not recommended. The built-in SQLite date and time functions do not work with
> TDateTime values.
>
> >0- Database: the datetime fields are floating numbers (REAL).
>
> Correct. Quote from the DISQLite3 Help -> SQLite3 Documentation -> Data
> Types:
>
> >>SQLite does not have a storage class set aside for storing dates and/or
> times. Instead, the built-in Date And Time Functions of SQLite are capable of
> storing dates and times as TEXT, REAL, or INTEGER values:
> >>
> >>* TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
> >>
> >>* REAL as Julian day numbers, the number of days since noon in Greenwich on
> November 24, 4714 B.C. according to the proleptic Gregorian calendar.
> >>
> >>* INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00
> UTC.
>
> For TDISQLite3UniDirQuery, I picked the Julian Date float format for a)
> smallest size, b) widest date range, and c) fastest conversation to and from
> TDateTime.
>
> >2- DISQLite3UniDirQuery InitFieldDef changes the type of the fields (ftFloat
> => ftDateTime)
>
> Correct also.
>
> >4- ClientDataSet has persistent TDateTimeFields (they must be persistent)
> >
> >The PROBLEM i have is the following:
> >
> >When i open the ClientDataset, TDISQLite3Database.sqlite3_cell16_asdatetime
> interprets the persistent TDateTimeFields always as JulianDates.
>
> This conversion is correct and required, assuming that your date time value
> is indeed stored as a Julian date.
>
> >Besides that, or as a consequence of that, i get then the exception:
> EIntOverflow (i.e. i do not know if the problems are one or two)
>
> I can only see this happen for invalid "Julian date" values. In your case,
> this is probably the case because you stored TDateTime floats to the
> database.
>
> >The QUESTION is: how can i have persistent fields in the ClientDataset in
> TDateTime format?
>
> This would require to modify TDISQLite3DataSet.pas and remove the Julian date
> to TDateTime conversion.
>
> >Notes:
> >
> >2) if there is no other way, the format of the data in the database can be
> changed in the way you prefer but in the ClientDataset the persistent fields
> should be TDateTime.
>
> This is the recommended way: Store all date time values as Julian days.
> Several conversion functions are available in DISQLite3Api:
>
> * DateTimeToJulianDate
> * DateToJulianDate
> * FileToJulianDate
> * FileTimeToJulianDate
>
> For reading with TDISQLite3UniDirQuery, set up the respective columns as
> ftDateTime in OnInitFieldDef as you are already doing. This will cause
> TDISQLite3UniDirQuery to convert the Julian dates to TDateTime automatically.
> The result should be compatible with other TDataSet components.
>
> Ralf
>
> _______________________________________________
> Delphi Inspiration mailing list
> yunqa@xxxxxxxxxxxxx
> //www.freelists.org/list/yunqa
>
>
>
>
--
edoardo falzetti
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
