Tsqlitedataset and time format as hh:mm:ss
lazarus / 16-Databases

----------------------------------------------------------------------
Sainty · 2005-12-20 08:32

Hello all,

1. I'm inserting data to field StartTime (defined in table 'calls' as
Time type):
...
SqliteDataSet.FieldByName('StartTime').AsDateTime   :=
StrToTime('18:59:59');
....
after all, using 'SELECT StartTime from calls' I get in my dbGrid time
in hh:mm format (without seconds). How I can get it displayed with
seconds?

2.  How to search database using time type? (SELECT)

Kind regards,

Sainty

----------------------------------------------------------------------
JanH · 2005-12-21 07:30

Hi Sainty,

1. In TDBGrid each column has a property named DisplayFormat. Use
'hh:mm:ss' to display hour:minute:second. Either you set this property
value at design time, or you dynamically set it at runtime.

2. Try statements like this:
  Code: [Select]SELECT * FROM calls WHERE starttime='10:11:12'
SELECT * FROM calls WHERE starttime>='10:11' and starttime<='10:12'
 

Greets,

Jan

----------------------------------------------------------------------
Marc · 2005-12-21 09:39

or better (if the db supports it)Code: [Select]SELECT * FROM calls
WHERE starttime BETWEEN '10:11:00' and '10:12:00'

----------------------------------------------------------------------
Anonymous · 2005-12-23 17:17

Quote from: "Marc"or better (if the db supports it)Code:
[Select]SELECT * FROM calls WHERE starttime BETWEEN '10:11:00' and
'10:12:00'

This wont work.
TSqliteDataset stores TDateTime as a float value.

----------------------------------------------------------------------
Marc · 2005-12-27 10:22

thats why I wrote: Quote(if the db supports it) :-)
It is more a generic remark that when a db supports it it is better to
use
  b BETWEEN a and c
than
  (a < b) and (b < c)

since the first can be done with a sinlge index lookup.

----------------------------------------------------------------------
LuizAmérico · 2006-01-04 23:13

Sqlite supports the BETWEEN operator. What i said is that
TSqliteDataset, which is a fpc wrapper around sqlite, stores the float
value (something like 1,23700000+E10) instead of the textual
representation (11:00:00) so it will compare different formats. Maybe
this will work (not tested):

'SELECT * FROM calls WHERE starttime BETWEEN '+
FloatToStr(StrToTime('10:11:00'))+
' and '+ FloatToStr(StrToTime('10:12:00'))

Luiz

----------------------------------------------------------------------
Anonymous · 2006-01-17 11:05

Thanks for your help. It's stored in float value and it works fine now
on SQLite 2.x.

Sainty