Storing and Displaying DateTime Values in SQLite DB

[new_x 1]

I am using FireDAC to handle a simple DB Program,

I am using TFDConnection, TFDQuery and TDataSource and TDBGrid components to handle DB operations.

It is a simple visitor recording DB application,
visitor table is created as;

1- create table visitors (name nvarchar(30) not null COLLATE NOCASE,
lastname nvarchar(30) not null COLLATE NOCASE,
enterance_time integer not null,
exit_time integer not null);

2- for the time record insertion into the table I used,

DateTimeToUnix(Now()) values for the enterance_time and exit_time fields.

But I do not know how to show normal date time string (like mm/dd/yyyy hh:mm:nn) in the DBGrid correponding fields when I want to list the records of the table to show to the user. So if I restate the problem "how can we display the date and time values in "mm/dd/yyyy format if the datetime value is stored in the Sqlite db as INTEGER"

 

Regards,

[Fr0sT.Brutal 900]

Try with custom TField.OnGetText.

[weirdo12 21]

Do you need to store exit_time value as INTEGER or would you prefer it stored in a format that you can read (like yyyy-mm-dd)?

[Hans J. Ellingsgaard 21]

If you store the values as a Timestamp field, you can show it in the grid with entrance_time.AsDateTime

[new_x 1]

6 hours ago, weirdo12 said:

Do you need to store exit_time value as INTEGER or would you prefer it stored in a format that you can read (like yyyy-mm-dd)?

I thought that I should store both enterance_time, exit_time values as Integer. Whenever a new record comes, I want to check if that new record's date time values falls into already stored records' date time values. This way it should be simple to compare date time values in Delphi am I right?

[new_x 1]

18 hours ago, Fr0sT.Brutal said:

Try with custom TField.OnGetText.

I am novice at using Delphi. How to assign that event? I am using FDConnection, FDQuery, DataSource and DBGrid, when querying the DB data, I am constructing SQL query statements (select * from ...)  based on user input, then executing that SQL statements with FDQuery showing the result in the DBGrid.

Which component does have that event?

Thanks 

Edited November 2, 2023 by new_x
More Explanation

[new_x 1]

5 hours ago, Hans J. Ellingsgaard said:

If you store the values as a Timestamp field, you can show it in the grid with entrance_time.AsDateTime

As I stated above I am novice at using Delphi, At which point in code, I should call  entrance_time.AsDateTime?

[Fr0sT.Brutal 900]

4 hours ago, new_x said:

Which component does have that event?

FDQuery.FieldByName('enterance_time').OnGetText

Or try something from this topic https://en.delphipraxis.net/topic/3458-firedac-sqlite-datetime-field/

[Serge_G 87]

First declare fields
Then set event on GetText for the field and code the event

 

procedure TForm1.FDTable1Date_exitGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
if Sender.IsNull then text:='n.d.'
                 else Text:=FormatDateTime('mm-dd-yyyy',UnixToDateTime(Sender.Value));
end;

on design date_exit should be an integer but at run time "magic" happens

 

Now let speak about another method for Timestamp type (my date_create)
You just have to set property displayformat

to get the date_create formatted at runtime but also at design time (yellow)

 

Edited November 2, 2023 by Serge_G

[Fr0sT.Brutal 900]

1 hour ago, Serge_G said:

You just have to set property displayformat

But for this the field must be of type TDateTimeField which AFAIU does not automatically happen for SQLite as the engine can't know whether an integer field is timestamp or just integer. So user has to create the field at runtime doing tricks with FieldDefs or to define it at design-time. But TC says he has dynamic queries so that's not an option

[Stano 143]

OT: you write that you are a newcomer. I quickly quit SQLite because of date problems. That was also the recommendation of experienced programmers.
Consider another DB.

[Serge_G 87]

To react to this, here is the structure I used

 

[weirdo12 21]

4 hours ago, Fr0sT.Brutal said:

But for this the field must be of type TDateTimeField which AFAIU does not automatically happen for SQLite as the engine can't know whether an integer field is timestamp or just integer. So user has to create the field at runtime doing tricks with FieldDefs or to define it at design-time. But TC says he has dynamic queries so that's not an option

If you use a suitable column type alias in your SQLite table definition, FireDAC will figure out the correct field data type to use.

 

DATE | SMALLDATE	dtDate
DATETIME | SMALLDATETIME	dtDateTime
TIMESTAMP	dtDateTimeStamp
TIME	dtTime

 

https://docwiki.embarcadero.com/RADStudio/Alexandria/en/SQLite_support_in_RAD_Studio

[new_x 1]

Thanks all of you for your comments.