SQLite3 Date Problem

[Thaddy]

The way I handle dates in any database is:
- I always use simply TDateTime,
- So I can store as a double. (TDateTime is a double)
- I always store as UTC
- And convert to local time for display only.
- Using FormatSettings
The reason is that this way the database table is always pure and works in any time zone.
SQLite3 can store doubles.
The above is actually what you would learn in any SQL database course regarding date and time.
That way I always have the correct local time in Amsterdam, London and Vilnius - where I happen to be quite often - or whatever time zone with the same code. That is what UTC is meant for.
The above is the same if you prefer to store unix timestamps. Store them as UTC.

[Zvoni]

My Opinion...

The way I handle dates in any database is:
- I always use simply TDateTime,  --> Agreed. If it's available, use it
- So I can store as a double. (TDateTime is a double) --> If no DateTime-type is available, i store it as Text in extended ISO-Format
- I always store as UTC  --> Disagree. To store as UTC only makes sense, if there are multiple Clients connecting from different timezones. If it's a local Database (SQLite!), it's local. Period! i use localtime, so the Data inside the DB is in sync with the Computer it resides on.
- And convert to local time for display only.  --> only if stored as UTC
- Using FormatSettings --> that goes without saying

[Thaddy]

Even for local databases - which SQLite isn't - it makes sense to always handle dates as UTC unless it is for your mother's agenda application.(i.e. hobby programmers)

[Zvoni]

Even for local databases - which SQLite isn't - it makes sense to always handle dates as UTC unless it is for your mother's agenda application.(i.e. hobby programmers)

Since when is SQLite NOT a local Database?

[egsuh]

I think poweld's solution is right. but,

Code: Pascal  [Select][+][-]

1.       SQLQuery.SQL.Text := 'SELECT xDate, xNumber from transactions';
2.       DataSource1.DataSet := SQLQuery1;
3.       DBGrid1.DataSource := DataSource1;
4.       SQLQuery1.Open;
5.       for i := 0 to DBGrid1.Columns.Count - 1 do
6.       begin
7.         if DBGrid1.Columns[i].Field.DataType = ftDate then
8.           DBGrid1.Columns[i].DisplayFormat := 'dd/mm/yyyy';
9.       end;
10.       //...

Only that ftDateTime instead of ftDate

        if DBGrid1.Columns[ i ].Field.DataType = ftDateTime then


might work, depending on SQLite's definition of the field type.
     

[silvercoder70]

Even for local databases - which SQLite isn't - it makes sense to always handle dates as UTC unless it is for your mother's agenda application.(i.e. hobby programmers)

Since when is SQLite NOT a local Database?

I'm with you Zvoni I regard it as local as well.

There are many ways this cat can be skinned (relatively speaking) ... (1) strftime() works, (2) setting up on DisplayText handler at the field level, or (3) setting displayFormat of column?

Also I would only though store a date/datetime as a numeric of sometime that easily allows for the date functions to be used on the field in a query, and that is whether I'm working in seconds, hours, days or months. Don't have to make it harder than it needs to be. (I also have a couple of vids on (1) and (2) as well.)