Where to put field formatting code ...

[jft]

This query is for a project using UniDAC version 4.1.4 VCL components (SQLiteProvider1, UniConnection1, UniDataSource1 & UniQuery1) and Delphi XE and Delphi's TDBGrid.

The SQLite3 database has 2 "datetime" columns and a number of float (double) columns. One of the "datetime" columns is an integer datatype storing the number of seconds since 01.01.1970 (ie Unix/Posix format) and the other is a double/real datatype storing the number of days since 30.12.1899 in the integer component and the time as a fraction of a day in the decimal part (Delphi DateTime format). The float columns need to be displayed with a fixed number of decimal places (SQLite truncates trailing 0's) and the date columns in international format (yyyy.mm.dd hh:mm).

There is no problem with the code to do the relevant conversions to the required display formats, that is already available - the question is where to put it (which component and which method) and the syntax to target a specific column and row.

Thanks for your help,
John

[AlexP]

Hello,

For the time being, to work with date and time in SQLite, if this data is stored in INTEGER and REAL fields, you should use mapping rules embedded in the SQLite engine, for example:

Code: Select all

CREATE TABLE [date_time] (
  [ID] DOUBLE, 
  [INTTIME] INT, 
  [REALTIME] REAL, 
  [date_time] DATETIME);

insert into date_time 
values (1,datetime('now','localtime') ,julianday('now','localtime'), datetime('now'));

select datetime(INTTIME), datetime(REALTIME), date_time from date_time;

We will investigate the possibility to add to Data Type Mapping a converter for work with such fields as DateTime without using explicit data type mapping